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-11-30 06:18:41
Message-ID: 20051130061841.GB23691@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Nov 29, 2005 at 10:53:38PM +0000, Simon Riggs wrote:
> On Tue, 2005-11-29 at 17:21 -0500, Tom Lane wrote:
> > regression=# explain select * from tenk1 where unique1 = any (array(select f1 from int4_tbl));

<snip>

> So we could teach the planner to transform:
> 
> IN (subselect) 
> 
> into
> 
> = ANY(array(subselect))
> 
> if we had the planner think the subselect had say < 1000 rows?

Do these constructs have the same semantics w.r.t. NULL? Currently
arrays can't have nulls but that is changing. Also, won't they behave
differently in the case where the subselect returns duplicate values?

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.

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

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2005-11-30 06:23:11
Subject: Re: slow IN() clause for many cases
Previous:From: Martijn van OosterhoutDate: 2005-11-30 06:11:15
Subject: Re: How to add our functions in postgres

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