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