PostGIS 3.5.2 ST_Contains() query on gist is slow

[Regina Obe]

> As noted by Paul our selectivity is just screwed up and probably has been
> since 3.4.
> I'm still surprised though that it chose to use a GIST over an exact btree where
> your node.id is the primary key.
> 
> I sadly stupidly dropped the gist index on mine to prove the point to myself
> that the speed is instantaneous after the gist drop.
> 
> If you kept the btree gist index, I'm curious how the speed is now that the gist
> one is gone if it would select the btree gist.
> 
> Also can you flip your filter to put it on the nodes table instead of the
> way_nodes table, I would expect this query to force it to use the primary key
> too and skip the  gist index.
> 
> SELECT wn.way_id FROM way_nodes AS wn, nodes AS n
>  	WHERE n.id = 8646506472 AND wn.node_id = n.id AND
> ST_Contains(ST_GeomFromEWKT('SRID=4326;POLYGON((6.1198199
> 52.612195500000006,6.1198199 53.2038323,7.0927397
> 53.2038323,7.0927397 52.612195500000006,6.1198199
> 52.612195500000006))'), n.geom);
> 
> 

Bas,

Never mind the test above I proposed, I tried it and it still decided to use the spatial index instead of the primary key on n.id
Which I found really surprising as I thought primary keys trump everything.

Anyway  Paul traced the commit that caused the issue and it was introduced in 3.4.

I just tested on your data and seemed to do the trick reverting containment to old behavior

ALTER OPERATOR @ (geometry , geometry)
    SET ( RESTRICT = contsel,
    JOIN = contjoinsel )
;

But other operators were changed as well so not a complete revert and we'll see if we can come up with a better solution.

I have the issue ticketed here:

https://trac.osgeo.org/postgis/ticket/5984