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 )
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 , 5 years ago
Description: | modified (diff) |
---|
comment:2 by , 5 years ago
Owner: | changed from | to
---|
comment:3 by , 5 years ago
comment:4 by , 5 years ago
Milestone: | PostGIS 3.0.0 → PostGIS 3.0.1 |
---|
comment:5 by , 5 years ago
Milestone: | PostGIS 3.0.1 → PostGIS 3.1.0 |
---|
comment:6 by , 4 years ago
Milestone: | PostGIS 3.1.0 → PostGIS 3.2.0 |
---|
comment:7 by , 3 years ago
Milestone: | PostGIS 3.2.0 → PostGIS Fund Me |
---|---|
Priority: | blocker → high |
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.