Postgres 13

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 SEQUENCE testing_thing_thing_id_seq START WITH 1;

CREATE TABLE testing_thing(
   thing_id integer NOT NULL DEFAULT nextval('testing_thing_thing_id_seq'),
   thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
   thing_desc varchar(100) NOT NULL
);

CREATE SEQUENCE

CREATE TABLE

INSERT INTO testing_thing (thing_num, thing_desc) VALUES (42,'foo'),(73,'bar');

SELECT * FROM testing_thing;

INSERT 0 2

thing_id
thing_num
thing_desc

1
42
foo

2
73
bar

SELECT 2

DROP TABLE testing_thing;

DROP TABLE

CREATE TABLE testing_thing(
   thing_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
   thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
   thing_desc varchar(100) NOT NULL
);

CREATE TABLE

INSERT INTO testing_thing (thing_num, thing_desc) VALUES (42,'foo'),(73,'bar');

SELECT * FROM testing_thing;

INSERT 0 2

thing_id
thing_num
thing_desc

1
42
foo

2
73
bar

SELECT 2