Re: select IN problem

From: Doug Silver <dsilver(at)quantified(dot)com>
To: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: select IN problem
Date: 2002-02-23 00:23:07
Message-ID: Pine.LNX.4.21.0202221613200.10661-100000@danzig.sd.quantified.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Patrick Hatcher 2002-02-24 00:19:36 PERL and PostgeSQL
Previous Message Andrew McMillan 2002-02-22 23:36:36 Re: select IN problem