Re: surprising query optimisation

On 12/05/2018 08:42 AM, Chris Withers wrote:
> On 05/12/2018 14:38, Stephen Frost wrote:
>> Greetings,
>>
>> * Chris Withers (chris(at)withers(dot)org) wrote:
>>> On 30/11/2018 15:33, Stephen Frost wrote:
>>>> * Chris Withers (chris(at)withers(dot)org) wrote:
>>>>> On 28/11/2018 22:49, Stephen Frost wrote:
>>>> 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?
>>
>> Not sure what you mean by 'break down a char field'.
>
> Rather than breaking into three buckets ('NEW', 'ACK', 'RSV'), a more
> complicated hierarchy ('N', 'NE', 'A', 'AC', etc).

The b-tree indexes on legacy RDBMS which I still occasionally fiddle with
performs key prefix compression in a manner similar to what you refer to,
but otherwise that's not how b-trees work.

--
Angular momentum makes the world go 'round.