Opened 5 months ago

Last modified 2 months ago

#4463 new defect

PostGIS raster ST_Intersects regression with pg12

Reported by: robe Owned by: robe
Priority: blocker Milestone: PostGIS 3.0.1
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 https://pub.data.gov.bc.ca/datasets/175624/92g/092g06_e.dem.zip https://pub.data.gov.bc.ca/datasets/175624/92g/092g06_w.dem.zip buildings https://data.vancouver.ca/datacatalogue/buildingFootprints.htm>

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
ALTER COLUMN geom 
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

SELECT b.*
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 http://irclogs.geoapt.com/postgis/%23postgis.2019-07-26.log 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 (4)

comment:1 Changed 5 months ago by robe

Description: modified (diff)

comment:2 Changed 3 months ago by robe

Owner: changed from Bborie Park to robe

comment:3 Changed 2 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 2 months ago by pramsey

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