Skip site navigation (1) Skip section navigation (2)

Re: RTREE Index on primary key generated by a sequence

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jean-Paul ARGUDO <jean-paul(dot)argudo(at)IDEALX(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: RTREE Index on primary key generated by a sequence
Date: 2002-01-25 17:28:02
Message-ID: 3C5195A2.C644E2F5@tm.ee (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-01-25 17:30:17
Subject: Re: PostgreSQL crashes with Qmail-SQL
Previous:From: Bruce MomjianDate: 2002-01-25 17:04:58
Subject: Re: new module contrib/tree for 7.2 ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group