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 region(
   id serial PRIMARY KEY,
   title VARCHAR (50) NOT NULL
);

CREATE TABLE unit(
   id serial PRIMARY KEY,
   region_id INT NOT NULL REFERENCES region(id)
);

CREATE TABLE unit_usage(
   id serial PRIMARY KEY,
   title VARCHAR (50) NOT NULL,
   unit_id INT NOT NULL REFERENCES unit(id)
);

CREATE INDEX ON unit ((region_id));
CREATE INDEX ON unit_usage ((unit_id));
CREATE INDEX ON unit_usage ((title));

INSERT INTO region(title) values('Europe'); -- ID = 1
INSERT INTO region(title) values('Asia'); -- ID = 2
INSERT INTO unit(region_id) values(1); -- ID = 1
INSERT INTO unit(region_id) values(2); -- ID = 2
INSERT INTO unit(region_id) values(2); -- ID = 3
INSERT INTO unit(region_id) values(2); -- ID = 4
INSERT INTO unit_usage(title, unit_id) values('a', 1); -- ID = 1
INSERT INTO unit_usage(title, unit_id) values('b', 1); -- ID = 2
INSERT INTO unit_usage(title, unit_id) values('a', 2); -- ID = 3
INSERT INTO unit_usage(title, unit_id) values('d', 3); -- ID = 4
INSERT INTO unit_usage(title, unit_id) values('b', 3); -- ID = 5
INSERT INTO unit_usage(title, unit_id) values('f', 3); -- ID = 6

WITH x AS
(
 select u.region_id as region_id, t.title as title
 from unit_usage t join unit u
 on t.unit_id = u.id
)
SELECT title, count(region_id) as found_in_regions
FROM x GROUP BY title;

explain WITH x AS
(
 select u.region_id as region_id, t.title as title
 from unit_usage t join unit u
 on t.unit_id = u.id
)
SELECT title, count(region_id) as found_in_regions
FROM x GROUP BY title;
set enable_hashjoin=off;

explain WITH x AS
(
 select u.region_id as region_id, t.title as title
 from unit_usage t join unit u
 on t.unit_id = u.id
)
SELECT title, count(region_id) as found_in_regions
FROM x GROUP BY title;

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

title	found_in_regions
d	1
a	2
b	2
f	1

QUERY PLAN
HashAggregate (cost=80.19..82.19 rows=200 width=126)
Group Key: t.title
-> Hash Join (cost=60.85..77.54 rows=530 width=122)
Hash Cond: (t.unit_id = u.id)
-> Seq Scan on unit_usage t (cost=0.00..15.30 rows=530 width=122)
-> Hash (cost=32.60..32.60 rows=2260 width=8)
-> Seq Scan on unit u (cost=0.00..32.60 rows=2260 width=8)

QUERY PLAN
HashAggregate (cost=137.59..139.59 rows=200 width=126)
Group Key: t.title
-> Merge Join (cost=39.44..134.94 rows=530 width=122)
Merge Cond: (u.id = t.unit_id)
-> Index Scan using unit_pkey on unit u (cost=0.15..82.06 rows=2260 width=8)
-> Sort (cost=39.28..40.61 rows=530 width=122)
Sort Key: t.unit_id
-> Seq Scan on unit_usage t (cost=0.00..15.30 rows=530 width=122)