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
> 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;
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 ...
Quantified Systems, Inc
In response to
pgsql-novice by date
|Next:||From: Andrew McMillan||Date: 2002-02-22 23:36:36|
|Subject: Re: select IN problem|
|Previous:||From: Andrew McMillan||Date: 2002-02-22 22:07:03|
|Subject: Re: indexes on multiple columns|