Opened 12 months ago

Last modified 7 months ago

#4463 new defect

PostGIS raster ST_Intersects regression with pg12

Reported by: robe Owned by: robe
Priority: blocker Milestone: PostGIS 3.1.0
Component: raster Version: master
Keywords: Cc:

Description (last modified by robe)

Our PostGIS raster still relies on inlining, so the costing we added to functions in PostgreSQL 12 is making raster performance poor as many uses of ST_Intersects (raster variant are no longer inlining)

Need to change raster ST_Intersects functions to be support function aware

Example from Paul's data:

DEMS buildings>

raster2pgsql -I -F -s 4269 -t 56x56 092g06_e.dem dem092g06e | psql yvr_raster
shp2pgsql -s 26910 -D building_footprints_2009 buildings
ALTER TABLE buildings
TYPE geometry(MultiPolygon, 4269)
USING ST_Transform(ST_Force2D(geom), 4269);

CREATE INDEX ix_buildings_geom_centroid ON buildings USING gist(ST_Centroid(geom));

--this doesn't use an index on PostgreSQL 12 (though it has option of a centroid functional index and a raster ST_ConvexHull(rast) index -- TODO: test on 11, we suspect it will be able to use the indexes

FROM buildings b
JOIN dem092g06e d
ON ST_Intersects(ST_Centroid(b.geom), rast)
WHERE ST_Value(d.rast, ST_Centroid(b.geom)) <= 30

The issue as RhodiumToad? explained on IRC is the ST_Intersects(raster,geometry), geometry,raster is still relying on _ST_Intersects which doesn't use the fancy function costing so is suffering from the high costs we've put in place.

Change History (5)

comment:1 Changed 12 months ago by robe

Description: modified (diff)

comment:2 Changed 10 months ago by robe

Owner: changed from Bborie Park to robe

comment:3 Changed 9 months ago by pramsey

This is a very large, intrusive piece of work, not suitable for beta period, push to a patch cycle, with enough testing it could be done in a patch release.

comment:4 Changed 9 months ago by pramsey

Milestone: PostGIS 3.0.0PostGIS 3.0.1

comment:5 Changed 7 months ago by robe

Milestone: PostGIS 3.0.1PostGIS 3.1.0
Note: See TracTickets for help on using tickets.