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 address (
    street text,
    zip text
);

INSERT INTO address VALUES
('a', '123'),
('b', '123'),
('c', '456'),
('d', '789'),
('e', '999');

SELECT * FROM address;
5 rows affected



street
zip


a
123

b
123

c
456

d
789

e
999


CREATE TABLE zip (
    zip text,
    name text
);

INSERT INTO zip VALUES
('123', 'A'),
('789', 'B'),
('999', 'C');

SELECT * FROM zip;
3 rows affected



zip
name


123
A

789
B

999
C


--- just demonstrating which zip is missing

SELECT
    *
FROM
    address a
LEFT JOIN
    zip z
ON a.zip = z.zip


street
zip
zip
name


a
123
123
A

b
123
123
A

c
456
null
null

d
789
789
B

e
999
999
C


SELECT DISTINCT ON (a.street, a.zip)
    *
FROM
    address a
JOIN
    zip z
ON a.zip <= z.zip
ORDER BY a.street, a.zip, z.zip


street
zip
zip
name


a
123
123
A

b
123
123
A

c
456
789
B

d
789
789
B

e
999
999
C