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

Re: performance of IN (subquery)

From: "Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>
To: Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: performance of IN (subquery)
Date: 2004-08-26 22:46:59
Message-ID: 20040826194628.H69548@ganymede.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
On Thu, 26 Aug 2004, Kevin Murphy wrote:

> I'm using PG 7.4.3 on Mac OS X.
>
> I am disappointed with the performance of queries like 'select foo from bar 
> where baz in (subquery)', or updates like 'update bar set foo = 2 where baz 
> in (subquery)'.  PG always seems to want to do a sequential scan of the bar 
> table.  I wish there were a way of telling PG, "use the index on baz in your 
> plan, because I know that the subquery will return very few results".   Where 
> it really matters, I have been constructing dynamic queries by looping over 
> the values for baz and building a separate query for each one and combining 
> with a UNION (or just directly updating, in the update case).  Depending on 
> the size of the bar table, I can get speedups of hundreds or even more than a 
> thousand times, but it is a big pain to have to do this.
>
> Any tips?
>
> Thanks,
> Kevin Murphy
>
> Illustrated:
>
> The query I want to do is very slow:
>
> select bundle_id from build.elements
> where elementid in (
> SELECT superlocs_2.element_id
>           FROM superlocs_2 NATURAL JOIN bundle_superlocs_2
>           WHERE bundle_superlocs_2.protobundle_id = 1);
> -----------
>      7644
>      7644
> (2 rows)
> Time: 518.242 ms

what field type is protobundle_id?  if you typecast the '1' to be the 
same, does the index get used?

Email: scrappy(at)hub(dot)org           Yahoo!: yscrappy              ICQ: 7615664

In response to

pgsql-general by date

Next:From: Gaetano MendolaDate: 2004-08-26 22:50:49
Subject: Re: Possible to insert quoted null value into integer field?
Previous:From: Jan WieckDate: 2004-08-26 22:35:06
Subject: Re: Possible to insert quoted null value into integer field?

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