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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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