On Fri, Nov 30, 2018 at 08:00 Chris Withers <chris@withers.org> wrote:
On 30/11/2018 12:55, Stephen Frost wrote: > > I'd suggest you check out the wiki article written about this kind of > > question: > > > > https://wiki.postgresql.org/wiki/Slow_Query_Questions > > > Have you tried a partial index on state!=‘RSV’?
The solution I originally posted, that we do easily enough at our query generation layer, is working perfectly, but this is good to know for next time.
My post here is mainly to try and understand what's going on so I can improve my general feel for how to use postgres at it's best.
So, why was the query ending up being a big scan rather than some quick lookups based on the index?
Thought that was mentioned already but at least part of the issue is that PG can’t just search for the other values when it’s a != in the index because it wouldn’t know what values to search for... PG doesn’t know, with complete certainty, that there’s only 3 values.
The partial index is something you should want anyway- that index won’t ever be used to search for RSV because it’s cheaper to just scan the table if we are looking for those, and the index will be much, much smaller without that common value being included.
