Given indexes applicable to multiple expressions in a WHERE condition, how does postgres decide which index is most beneficial to use?
The author of that SO post tried to adjust the default statistics target, presumably to convince postgres to use the faster primary key index, instead of the slower gist index on the hstore values, but this didn't work.
I beg your pardon. The problem is more or less clear to me, but the solution is not. What does the "hack is to add an "offset 0" to the query" suggest? Thank you.
A subquery with a LIMIT clause cannot have where clause expressions in upper parts of the query tree pushed down it without changing the overall query result - something the planner is not allowed to do. For the hack, since adding an actual LIMIT clause doesn't make sense you omit it, but still add the related OFFSET clause so the planner still treats the subquery as a LIMIT subquery. And since you don't want to skip any rows you specify 0 for the offset.