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[@]dcc.uchile.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) ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
