Re: select IN problem

On Sat, 2002-02-23 at 10:36, Doug Silver wrote:
> I've read the IN chapter in Bruce M.'s Postgresql book, but I still can't
> seem to get my select/IN to work. I have two tables, transactions and
> transactions_detail, with the transaction_id field as the reference in
> the transactions_detail table.
>
> # select transaction_id from transactions where enter_date> cast('2002-02-22' as date);
> transaction_id
> ----------------
> 2043
> 2044
> 2045
>
> purchases=# select transaction_id from transactions_detail where transaction_id>2042;
> transaction_id
> ----------------
> 2043
> 2044
> 2045
>
> purchases=# \d transactions_detail
> Table "transactions_detail"
> Attribute | Type | Modifier
> ----------------+-----------------------+----------
> transaction_id | smallint |
> products | character varying(20) |
> quantities | smallint |
>
> But the following query causes it to hang, after 10 seconds I finally stop
> it.
>
> purchases=# select transaction_id from transactions_detail where
> purchases=# transaction_id IN (
> purchases=# select transaction_id from transactions where enter_date> cast('2002-02-22' as date)
> purchases=# );
>
> Any suggestions?

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.
--
--------------------------------------------------------------------
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?