PostGIS raster ST_Intersects regression with pg12

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