Re: Query much faster with enable_seqscan=0

On Sep 21, 2010, at 6:30 PM, Tom Lane wrote:

> Ogden <lists@darkstatic.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_resultand a GROUP BY. I still am not happy with my query - the EXISTS executes in great speed however
Icannot retrieve any of the columns from that table.  

Thank you

Ogden