Re: surprising query optimisation

Greetings,

* Chris Withers (chris(at)withers(dot)org) wrote:
> On 28/11/2018 22:49, Stephen Frost wrote:
> >* Chris Withers (chris(at)withers(dot)org) wrote:
> >>We have an app that deals with a lot of queries, and we've been slowly
> >>seeing performance issues emerge. We take a lot of free form queries from
> >>users and stumbled upon a very surprising optimisation.
> >>
> >>So, we have a 'state' column which is a 3 character string column with an
> >>index on it. Despite being a string, this column is only used to store one
> >>of three values: 'NEW', 'ACK', or 'RSV'.
> >
> >Sounds like a horrible field to have an index on.
>
> That's counter-intuitive for me. What leads you to say this and what would
> you do/recommend instead?

For this, specifically, it's because you end up with exactly what you
have: a large index with tons of duplicate values. Indexes are
particularly good when you have high-cardinality fields. Now, if you
have a skewed index, where there's one popular value and a few much less
popular ones, then that's where you really want a partial index (as I
suggest earlier) so that queries against the non-popular value(s) is
able to use the index and the index is much smaller.

Of course, for this to work you need to set up the partial index
correctly and make sure that your queries end up using it.

Thanks!

Stephen