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

Re: slow IN() clause for many cases

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
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 08:18:44
Message-ID: 1133511524.2906.520.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, 2005-11-30 at 07:18 +0100, Martijn van Oosterhout wrote:

> And finally, why can't:
> 
> > > > Select * From Sales where month IN (
> > > > select month from time_dimension where FinYear = 2005 and Quarter = 3)
> 
> Be written as: 
> 
> Select sales.* From Sales, time_dimension 
> where month = time_dimension.inYear = 2005 and time_dimension.Quarter = 3;
> 
> As long as there are no NULLs it returns the same as the IN() version
> and PostgreSQL can optimise it just fine.

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.

Best Regards, Simon Riggs


In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2005-12-02 08:28:19
Subject: Re: [HACKERS] Should libedit be preferred to libreadline?
Previous:From: Michael GlaesemannDate: 2005-12-02 07:33:01
Subject: Buildfarm: Bear, Branch 2?

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