Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE foo ( x int PRIMARY KEY );

CREATE TABLE

CREATE TABLE bar (
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ON DELETE CASCADE
);

CREATE TABLE

INSERT INTO foo VALUES (3), (45), (7);

INSERT 0 3

INSERT INTO bar VALUES(3), (3), (45), (7), (7);

INSERT 0 5

SELECT * FROM foo;

x

3

45

7

SELECT 3

SELECT * FROM bar;

x

3

3

45

7

7

SELECT 5

DELETE FROM foo WHERE x = 3;

DELETE 1

DROP TABLE foo CASCADE;

DROP TABLE

SELECT * FROM foo;

ERROR:  relation "foo" does not exist
LINE 1: SELECT * FROM foo;
                      ^

SELECT * FROM bar;

x

45

7

7

SELECT 3