Postgres 14

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?.

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE SEQUENCE user_id_seq;
CREATE SEQUENCE address_id_seq;
CREATE TABLE user_
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
    CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
);
CREATE TABLE address
(
    id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
    address character varying COLLATE pg_catalog."default" NOT NULL,
    user_id integer,
    CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
    CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY (user_id)
        REFERENCES user_ (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);
WITH insert_user AS
(
  INSERT INTO user_ (email) VALUES ('user1@example.com')
  RETURNING id
)
SELECT *  FROM insert_user;


id


1


SELECT * FROM user_;


id
email


1
user1@example.com


TRUNCATE user_ CASCADE;
BEGIN TRANSACTION;
WITH insert_user AS
(
  INSERT INTO user_ (email) VALUES ('user2example.com')
  RETURNING id
)
INSERT INTO address (address, user_id)
SELECT '1, Long Street, Somewhere', (SELECT id FROM insert_user)
COMMIT;
1 rows affected

SELECT * FROM user_;


id
email


2
user2example.com


SELECT * FROM address;


id
address
user_id


1
1, Long Street, Somewhere
2