Postgres 12

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 tbl (id int, Source text, Mode text, SponsorID int, Code int);
INSERT INTO tbl VALUES
    (1, 'source1', 'Paper', 123, 7102),
    (2, 'source2', 'Paper', 123, 7102),
    (3, 'source2', 'Paper', 123, 7101),
    (4, 'source1', 'Paper', 123, 7101),
    (5, 'source2', 'Paper', 123, 1010),
    (6, 'source1', 'Paper', 345, 1010),
    (7, 'source2', 'Paper', 345, 7102),
    (8, 'source1', 'Paper', 345, 1010),
    (9, 'source2', 'Paper', 345, 7102),
    (10, 'source1', 'Paper', 345, 7102),
    (11, 'source1', 'Paper', 678, 1010),
    (12, 'source2', 'Paper', 678, 1010),
    (13, 'source1', 'Paper', 678, 1010),
    (14, 'source2', 'Paper', 678, 1010),
    (15, 'source1', 'Paper', 678, 1010);

-- should exist???
CREATE TABLE sponsor (id int);
INSERT INTO sponsor VALUES (123),(345),(678);
15 rows affected

3 rows affected

CREATE INDEX ON tbl (code);

SELECT DISTINCT ON (SponsorID) t.*
       -- array ordered by sort preference
FROM   unnest('{7101, 7102, 1010}'::int[]) WITH ORDINALITY x(code, ord)
JOIN   tbl t USING (code)
ORDER  BY t.SponsorID, x.ord, t.id;


id
source
mode
sponsorid
code


3
source2
Paper
123
7101

7
source2
Paper
345
7102

11
source1
Paper
678
1010


CREATE INDEX ON tbl (SponsorID, code, id); -- !

SELECT t.*
FROM   sponsor s
CROSS  JOIN LATERAL (
             (SELECT * FROM tbl t WHERE t.SponsorID = s.id AND code = 7101 ORDER BY id LIMIT 1) -- ORDER BY id???
   UNION ALL (SELECT * FROM tbl t WHERE t.SponsorID = s.id AND code = 7102 ORDER BY id LIMIT 1)
   UNION ALL (SELECT * FROM tbl t WHERE t.SponsorID = s.id AND code = 1010 ORDER BY id LIMIT 1)
   LIMIT 1
   ) t
;


id
source
mode
sponsorid
code


3
source2
Paper
123
7101

7
source2
Paper
345
7102

11
source1
Paper
678
1010

id	source	mode	sponsorid	code
3	source2	Paper	123	7101
7	source2	Paper	345	7102
11	source1	Paper	678	1010