From: | Ogden <lists(at)darkstatic(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query much faster with enable_seqscan=0 |
Date: | 2010-10-13 00:23:24 |
Message-ID: | 9EE3D98A-CF01-43A3-B3AC-636F5D407FA8@darkstatic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Sep 21, 2010, at 6:30 PM, Tom Lane wrote:
> Ogden <lists(at)darkstatic(dot)com> writes:
>> SELECT tr.id, tr.sid
>> FROM
>> test_registration tr,
>> INNER JOIN test_registration_result r on (tr.id = r.test_registration_id)
>> WHERE.
>> tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid
>> GROUP BY tr.id, tr.sid
>
> Seeing that tr.id is a primary key, I think you might be a lot better
> off if you avoided the inner join and group by. I think what you really
> want here is something like
>
> SELECT tr.id, tr.sid
> FROM
> test_registration tr
> WHERE
> tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid
> AND EXISTS(SELECT 1 FROM test_registration_result r
> WHERE tr.id = r.test_registration_id)
>
> regards, tom lane
>
Thank you for this suggestion, however, what if I wanted some columns from test_registration_result - this wouldn't work, for example if I wanted test_registration_result.answer to be fetched. Hence, I had to have a JOIN with test_registration_result and a GROUP BY. I still am not happy with my query - the EXISTS executes in great speed however I cannot retrieve any of the columns from that table.
Thank you
Ogden
From | Date | Subject | |
---|---|---|---|
Next Message | Samuel Gendler | 2010-10-13 02:28:55 | Re: Query much faster with enable_seqscan=0 |
Previous Message | Dan Harris | 2010-10-13 00:00:11 | Re: Slow count(*) again... |