Re: surprising query optimisation

On 30/11/2018 15:33, Stephen Frost wrote:
> 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.

Interesting! In my head, for some reason, I'd always assumed a btree
index would break down a char field based on the characters within it.
Does that never happen?

If I changed this to be an enum field, would != still perform poorly or
can the query optimiser spot that it's an enum and just look for the
other options?

cheers,

Chris