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

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-22 22:15:52
Message-ID: Pine.LNX.4.21.0202221413060.10661-100000@danzig.sd.quantified.net (view raw or flat)
Thread:
Lists: pgsql-novice
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?

Thanks, #2 worked as I should have tried something like that earlier.  
Still a bit rusty with my sql queries ...

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


In response to

Responses

pgsql-novice by date

Next:From: Andrew McMillanDate: 2002-02-22 23:36:36
Subject: Re: select IN problem
Previous:From: Andrew McMillanDate: 2002-02-22 22:07:03
Subject: Re: indexes on multiple columns

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