Hey Laurenz, Tom - thanks again !
> that it is cheaper to use the index that supports the ORDER BY
Thing is, that both queries use the exact same index (idx_hashes), but one uses it w/ the filter and one does not.
> This doesn't match up terribly well with the table definition you showed before
Yeah.. it was a bit hard to reproduce exactly, but the fiddle does showcase that there's some threshold to the ANY set-size
where it stops using the column in the index condition, and moves it to the filter step - I thought it might originate
from similar reasons.
> but I wonder whether tidh is a low-order index column.
The index indeed uses tidh as a low order column, and it's better to have it the other way around -
currently, it's: (tid, pidh, tidh) - where (tid, tidh, pidh) would've probably worked better.
We've already optimized the query itself - but for pure understanding of the planner decision here,
I'd really still like to understand, if possible, the difference between ANY and IN,
and why, even though the column order isn't optimal - one plan still successfully uses the index more efficiently than another.
Any idea where I could zone-in in the source code to look for hints, maybe ?
Appreciate it !
Danny
