Re: select IN problem

On 23 Feb 2002, Andrew McMillan wrote:

> It will eventually return something. My guess is that you probably have
> a lot of records in one or both tables.
>
> Do a "VACUUM ANALYZE" and then take a look at the EXPLAIN ... output for
> the three queries to get a real example of the differences in execution
> plans. Analysis for queries with sub-plans is more complex than the
> normal case, however.
>
> What your query would have been doing (I think) is running the subselect
> for every row in the transactions_detail table. That would probably be
> about the worst possible case you can imagine, hence the bad query time.
>
> Check the archives of -hackers to see more information about problems
> with making IN (...) use an index.
>
>
> > Thanks, #2 worked as I should have tried something like that earlier.
> > Still a bit rusty with my sql queries ...
>
> We've all been there... :-)
>
> Did my first suggestion not work at all? I think that (in this case) #2
> is probably the most efficient, but it's worth understanding the EXISTS
> syntax as you can generally turn an IN (...) into EXISTS (...) and
> sometimes it _is_ the best way.
>
> Cheers,
> Andrew.
>

yes, the exists statement worked but much slower. Both tables are quite
small (~2000 entries) so the delay was surprising.

I assume that's what this result corresponds to:
explain select transaction_id from transactions_detail
where transaction_id IN
(select transaction_id from transactions where enter_date>cast('2002-02-20' as date));

NOTICE: QUERY PLAN:

Seq Scan on transactions_detail (cost=0.00..84701.18 rows=2062 width=2)
SubPlan
-> Seq Scan on transactions (cost=0.00..82.11 rows=625 width=4)

EXPLAIN

So it's doing a sequential scan on the trans_detail against the result of the
SubPlan. ouch.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Quantified Systems, Inc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~