[postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade

[Paul Ramsey]

> On Feb 4, 2020, at 9:12 AM, Regina Obe <lr at pcorp.us> wrote:
> 
> Thanks for the report.  I've ticketed as a bug - https://trac.osgeo.org/postgis/ticket/4635

OK, cleaning this out a little. 
Ticket for reference https://trac.osgeo.org/postgis/ticket/4635#comment:2

First, drop all the rest of your testing SQL and just test the contents of the bb_edge CTE. That seems to be sufficient, and it’s the only spatial part.
Second, for testing 3.0, drop the use of the &&& operator and see what happens with just the ST_DWithin3D function call, which should be throwing an index op in there implicitly.

SELECT Count(*) FROM (
 SELECT te.id, te.parent_id
    FROM treenode_edge te
    WHERE ST_3DDWithin(te.edge, ST_MakePolygon(ST_MakeLine(ARRAY[
        ST_MakePoint(471548.0,  290140.0,    160420.0),
        ST_MakePoint(542460.0, 290140.0,    160420.0),
        ST_MakePoint(542460.0, 330140.0, 160420.0),
        ST_MakePoint(471548.0,  330140.0, 160420.0),
        ST_MakePoint(471548.0,  290140.0,    160420.0)]::geometry[])),
        20.0)
) a;

We are definitely seeing different plans in that CTE. 

Testing for 12/3: 

12/3 is picking an index scan on the spatial index, which is turning out to be slower. 
You can adjust the COST of the ST_3DDWithin down: default cost is 10000. 
Where does the plan change as you adjust it down? 
ALTER FUNCTION ST_3DDWithin COST 9000;

A big change between 2.5 and 3.0 was costing on spatial functions, so seeing different plans is not surprising, especially since the mechanisms for building the plan have changed so much (SQL wrapper functions vs Pg12 support functions). It’s possible that with the cost of the function set so high, and actually being seen by the planner now, we’re getting an index scan that, in this case, is less efficient than the alternative.

P