Re: surprising query optimisation

Greetings,

* Ron (ronljohnsonjr(at)gmail(dot)com) wrote:
> On 12/05/2018 08:42 AM, Chris Withers wrote:
> >On 05/12/2018 14:38, Stephen Frost wrote:
> >>>>* Chris Withers (chris(at)withers(dot)org) wrote:
> >>>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.

There's been some discussion of prefix compression in PostgreSQL. Even
with that, though, it hardly seems sensible to have an index which has
tons of duplicates comprising most of the index, and a != would still
have to search the index to make sure there aren't any entries which
need to be returned..

Now, maybe once we get skipping scans where we would be able to skip
over a large chunk of the index because it's just tons of duplicates
without having to visit everything along the way, then maybe having this
inefficient index would "just" take up disk space, but why waste that
space?

Thanks!

Stephen