Re: performance of IN (subquery)

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@hub.org           Yahoo!: yscrappy              ICQ: 7615664