[postgis-tickets] [PostGIS] #4463: PostGIS raster ST_Intersects regression with pg12

[PostGIS]

#4463: PostGIS raster ST_Intersects regression with pg12
----------------------+---------------------------
  Reporter:  robe     |      Owner:  Bborie Park
      Type:  defect   |     Status:  new
  Priority:  blocker  |  Milestone:  PostGIS 3.0.0
 Component:  raster   |    Version:  trunk
Resolution:           |   Keywords:
----------------------+---------------------------
Description changed by robe:

Old description:

> 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 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.

New description:

 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.

--

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4463#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.