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

select version();


version


PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


CREATE TABLE "locations" (
    "code" varchar(10),
    "location_name" varchar(50)
);

INSERT INTO "locations" ("code", "location_name") VALUES ('KL', 'Kuala Lumpur'),
('SG', 'Singapore'),
('JKR', 'Jakarta');
3 rows affected

CREATE TABLE "payments" (
    "location_code" varchar(10),
    "amount" float8
);

INSERT INTO "payments" ("location_code", "amount") VALUES ('KL', '50'),
('SG', '40'),
('KL', '50');
3 rows affected

SELECT
	l.code,
	SUM(p.amount)
FROM
	locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.code


code
sum


KL
100

SG
40

JKR
null


SELECT
	l.code,
	l.location_name,
	SUM(p.amount)
FROM
	locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.location_name, l.code


code
location_name
sum


JKR
Jakarta
null

KL
Kuala Lumpur
100

SG
Singapore
40