Re: Index optimization ?

On Wed, Jan 19, 2005 at 02:15:42AM +0100, Florian G. Pflug wrote:
> You can, howevery, accelerate something like "where f in (1,2,3,4)". You
> just scan the index 4 times, each time for a different value. Of course,
> if the number of values becomes larger and larger, there is a point
> where it's more efficient to do a sequential scan _once_, instead of a
> few tousand index scans (depends on the number of rows in the table).
> The postgres optimizer tries to estimate this, and will switch to an
> seq-scan, if it would have to do too many index lookups.

Are PostgreSQL Btree indexes setup as a linked-list so you can scan
forwards and backwards in them? If so, is the IN processor smart enough
to collapse ranges of values into a single index scan (ie,
IN(1,2,3,4,8,9,10) would best be done as an index scan starting at 1 and
stoping at >4 and a second scan starting at 8 and stopping at >10).
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"