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

Re: slow IN() clause for many cases

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, andrew(at)supernews(dot)com,pgsql-hackers(at)postgresql(dot)org
Subject: Re: slow IN() clause for many cases
Date: 2005-12-02 09:48:16
Message-ID: 20051202094816.GA3057@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, Dec 02, 2005 at 08:18:44AM +0000, Simon Riggs wrote:
> It can, of course, but there must be value in that optimization.
> 
> If you consider how IN () would be transformed into
> =ANY(ARRAY(subselect)) you'll see that the subselect values would be
> treated as constants that could result in a bitmap index lookup.
> 
> Transforming IN () into straight joins would not take the same approach
> when more than one join (i.e. 3 or more tables) was requested.

Are you sure? If you have one table joined to many others, that is the
single most obvious case for bitmap indexes. And joins are converted to
bitmap index scans all the time so I'm unsure why this case would be
any different. If the results are the same, the optimiser should
optimise both the same, no?

Anyway, maybe I'm just old fashioned in thinking that joins are by far
the easiest to optimise because they are closest to relational algebra.
IN() can also be converted to a join, except for the NULL effect.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

pgsql-hackers by date

Next:From: Csaba NagyDate: 2005-12-02 10:07:06
Subject: Re: generalizing the planner knobs
Previous:From: Simon RiggsDate: 2005-12-02 09:39:06
Subject: Re: Using multi-row technique with COPY

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