Opened 5 years ago

Last modified 3 years ago

#4463 new defect

PostGIS raster ST_Intersects regression with pg12

Reported by: robe Owned by: robe
Priority: high Milestone: PostGIS Fund Me
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 (7)

comment:1 by robe, 5 years ago

Description: modified (diff)

comment:2 by robe, 5 years ago

Owner: changed from Bborie Park to robe

comment:3 by pramsey, 4 years ago

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 by pramsey, 4 years ago

Milestone: PostGIS 3.0.0PostGIS 3.0.1

comment:5 by robe, 4 years ago

Milestone: PostGIS 3.0.1PostGIS 3.1.0

comment:6 by robe, 3 years ago

Milestone: PostGIS 3.1.0PostGIS 3.2.0

comment:7 by Algunenano, 3 years ago

Milestone: PostGIS 3.2.0PostGIS Fund Me
Priority: blockerhigh
Note: See TracTickets for help on using tickets.