Re: Query much faster with enable_seqscan=0

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