Postgres 15

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 t (g1 geometry(polygon,4326), g2 geometry(polygon,4326));
INSERT INTO t 
VALUES ('SRID=4326;POLYGON((-4.562770118602959 54.27357897197032,-4.510585060009209 54.27357897197032,-4.510585060009209 54.245904615218556,-4.562770118602959 54.245904615218556,-4.562770118602959 54.27357897197032))'::geometry,
        'SRID=4326;POLYGON((-4.529811134227959 54.258340319807985,-4.483119239696709 54.258340319807985,-4.483119239696709 54.234267819401126,-4.529811134227959 54.234267819401126,-4.529811134227959 54.258340319807985))'::geometry)

CREATE TABLE

INSERT 0 1

SELECT ST_Overlaps(g1,g2) FROM t;

st_overlaps

t

SELECT 1

SELECT 
  ST_Area(ST_Intersection(g1,g2))/ST_Area(g1)*100 AS perc_overlap,
  ST_Intersection(g1,g2)
FROM t 

perc_overlap
st_intersection

16.555309365086124
0103000020E6100000010000000500000054435ACFD60A12C04B266CCD791F4B4054435ACF861E12C04B266CCD791F4B4054435ACF861E12C02068AC4B11214B4054435ACFD60A12C02068AC4B11214B4054435ACFD60A12C04B266CCD791F4B40

SELECT 1

-- greater than 15%
SELECT * FROM t
WHERE ST_Area(ST_Intersection(g1,g2))/ST_Area(g1)*100 > 15 
  
g1
g2

0103000020E6100000010000000500000054435ACF464012C030BEC0A204234B4054435ACFD60A12C030BEC0A204234B4054435ACFD60A12C04B266CCD791F4B4054435ACF464012C04B266CCD791F4B4054435ACF464012C030BEC0A204234B40
0103000020E6100000010000000500000054435ACF861E12C02068AC4B11214B4054435ACFB6EE11C02068AC4B11214B4054435ACFB6EE11C0A26AE77CFC1D4B4054435ACF861E12C0A26AE77CFC1D4B4054435ACF861E12C02068AC4B11214B40

SELECT 1