Re: Index optimization ?

On Tue, Jan 18, 2005 at 07:33:51PM -0600, Jim C. Nasby wrote:
> 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?

Yes, they are.

> If so, is the IN processor smart enough to collapse ranges of values
> into a single index scan

No, it isn't AFAIK.

> (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).

That assumes the optimizer knows that the domain can contain integer
values ... seems a complex and infructuous analysis to do in general.

Maybe the optimizer could collapse that to a single index scan from 1 to
10 and then apply a filter to extract only the values actually mentioned.
Not sure how workable that is.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)