Postgres 11

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 cpt (
  recid int PRIMARY KEY
, cdesc int NOT NULL
);

CREATE TABLE lab (
  lab_id    int PRIMARY KEY
, cpt_recid int
, CONSTRAINT cs_cpt FOREIGN KEY (cpt_recid)
   REFERENCES cpt (recid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT
);

INSERT INTO cpt (recid, cdesc) VALUES
  (1, 1)  -- master
, (2, 1)  -- dupe
, (3, 1)  -- dupe
, (4, 2)  -- master
, (5, 2)  -- dupe
, (6, 3)
, (7, 4);

INSERT INTO lab (lab_id, cpt_recid) VALUES
  (11, 1)
, (12, 2)  -- reference to dupe
, (13, 3)  -- reference to dupe
, (14, 4)
, (15, 5)  -- reference to dupe
, (16, 6)
, (17, 6);
7 rows affected

7 rows affected

WITH plan AS (
   SELECT *
   FROM  (
      SELECT recid, min(recid) OVER (PARTITION BY cdesc) AS master_recid
      FROM   cpt
      ) sub
   WHERE  recid <> master_recid  -- ... <> self
   )
 , upd_lab AS (
   UPDATE lab l
   SET    cpt_recid = p.master_recid   -- link to master recid ...
   FROM   plan p
   WHERE  l.cpt_recid = p.recid
   )
DELETE FROM cpt c
USING  plan p
WHERE  c.recid = p.recid
RETURNING c.recid;


recid


2

3

5


TABLE cpt ORDER BY recid;


recid
cdesc


1
1

4
2

6
3

7
4


TABLE lab ORDER BY lab_id;


lab_id
cpt_recid


11
1

12
1

13
1

14
4

15
4

16
6

17
6