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

Re: select IN problem

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Doug Silver <dsilver(at)quantified(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: select IN problem
Date: 2002-02-22 23:36:36
Message-ID: 1014420996.3232.2530.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-novice
On Sat, 2002-02-23 at 11:15, Doug Silver wrote:
> On 23 Feb 2002, Andrew McMillan wrote:
> > 
> > SELECT td.transaction_id FROM transactions_detail td
> > WHERE EXISTS (SELECT transaction_id FROM transactions t
> >    WHERE t.transaction_id = td.transaction_id
> >      AND t.enter_date > CAST('2002-02-02' AS DATE );
> > 
> > Could well work better.  The problem you are likely to be encountering
> > is that IN (...) will not use an index.
> > 
> > To see the query plans generated by the different SQL, use 'EXPLAIN <sql
> > command>' - it is _well_ worth coming to grips with what EXPLAIN can
> > tell you.
> > 
> > You could also be better with a plan that did a simple JOIN and
> > DISTINCT:
> > 
> > SELECT DISTINCT td.transaction_id 
> > FROM transactions_detail td, transactions t
> > WHERE t.enter_date > '2002-02-02'
> >   AND td.transaction_id = t.transaction_id;
> > 
> > Regards,
> > 					Andrew.
> > 
> 
> Ok, the transactions table does have an index, so that must be the problem
> there, but should it give an error or will it eventually return something?

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.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


In response to

Responses

pgsql-novice by date

Next:From: Doug SilverDate: 2002-02-23 00:23:07
Subject: Re: select IN problem
Previous:From: Doug SilverDate: 2002-02-22 22:15:52
Subject: Re: select IN problem

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