PostGIS 3.5.2 ST_Contains() query on gist is slow

[Regina Obe]

> Dropping the index is not what you want, because you still want it for other
> queries. You do want a way to not use the index for this query where it's
> detrimental.
> 
> Isn't the OFFSET 0 trick a way to manipulate the query planner to avoid using
> the detrimental index on its first try?
> 
> Is there a better way? I read your post [0] about the clever postgres query
> planning, which suggests that ANALYZE should suffice.
> 
> [0] https://www.crunchydata.com/blog/indexes-selectivity-and-statistics
> 
> Kind Regards,
> 
> Bas
> 
> --
>   GPG Key ID: 4096R/6750F10AE88D4AF1
> Fingerprint: 8182 DE41 7056 408D 6146  50D1 6750 F10A E88D 4AF1

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);