Postgres 13

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 a (
    id int,
    geom int4range
);

INSERT INTO a VALUES
(1, '[1,3]'),
(2, '[5,7]'),
(3, '[9,12]'),
(4, '[100,130]');

SELECT * FROM a
4 rows affected



id
geom


1
[1,4)

2
[5,8)

3
[9,13)

4
[100,131)


CREATE TABLE b (
    id int,
    geom int4range
);

INSERT INTO b VALUES
(1, '[2,4]'),
(2, '[4,6]'),
(3, '[6,11]');

SELECT * FROM b
3 rows affected



id
geom


1
[2,5)

2
[4,7)

3
[6,12)


SELECT
    *,
    a.geom + b.geom
FROM a
JOIN b ON a.geom && b.geom


id
geom
id
geom
?column?


1
[1,4)
1
[2,5)
[1,5)

2
[5,8)
2
[4,7)
[4,8)

2
[5,8)
3
[6,12)
[5,12)

3
[9,13)
3
[6,12)
[6,13)


SELECT DISTINCT ON (b.id)
    a.id,
    a.geom + b.geom
FROM a
JOIN b ON a.geom && b.geom


id
?column?


1
[1,5)

2
[4,8)

2
[5,12)

id	geom	id	geom	?column?
1	[1,4)	1	[2,5)	[1,5)
2	[5,8)	2	[4,7)	[4,8)
2	[5,8)	3	[6,12)	[5,12)
3	[9,13)	3	[6,12)	[6,13)