Re: RTREE Index on primary key generated by a sequence

Tom Lane wrote:
>
> Jean-Paul ARGUDO <jean-paul(dot)argudo(at)IDEALX(dot)com> writes:
> > Since my english is not so fluent, I found on the net a little
> > explication about Reverse Key Indexes (not RTREE, sorry :).
>
> > As an explication, you could read there the point 9 :
> > http://oracle.oreilly.com/news/oraclepp_0900.html
>
> > ... Reverse key indexes reverse the
> > bytes in each index entry, causing
> > sequential entries to be dispersed across the
> > index tree.
>
> Hmm. I think you could implement that with a custom index opclass
> consisting of operators that flipped the bytes before comparison.
> Not clear that it'd really buy you enough to be worth the trouble,
> but if someone wants to try it...
>
> > ... One
> > trade-off involved with using this
> > technique is that by its nature, reverse key indexes
> > cannot be used as the basis for an index
> > range scan.
>
> The way this would show up in Postgres is that you would use the
> standard integer = operator as the = member of the opclass, but
> all the other members would be byte-reversed-comparison operators
> that would never be useful in real-world queries.

Never is too strong word. I guess that if index block contention is
serious enough problem for insert-intensive aplication and you need
only = access then this could be a good thing.

OTOH it will probably be not so good for cache usage and update
locality so it may not be as good as it sounds even for the above
scenario.

---------------
Hannu