Re: select IN problem
От | Andrew McMillan |
---|---|
Тема | Re: select IN problem |
Дата | |
Msg-id | 1014414921.3232.2399.camel@kant.mcmillan.net.nz обсуждение исходный текст |
Ответ на | select IN problem (Doug Silver <dsilver@quantified.com>) |
Ответы |
Re: select IN problem
(Doug Silver <dsilver@quantified.com>)
|
Список | 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 по дате отправления: