Re: select IN problem - Mailing list pgsql-novice
From | Andrew McMillan |
---|---|
Subject | Re: select IN problem |
Date | |
Msg-id | 1014414921.3232.2399.camel@kant.mcmillan.net.nz Whole thread Raw |
In response to | select IN problem (Doug Silver <dsilver@quantified.com>) |
Responses |
Re: select IN problem
|
List | pgsql-novice |
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?
pgsql-novice by date: