Re: Query much faster with enable_seqscan=0

On Tue, Sep 21, 2010 at 4:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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
>
>
Could you explain the logic behind why this structure is better than the
other? Is it always the case that one should just always use the
'exists(select 1 from x...)' structure when trying to strip rows that don't
join or is it just the case when you know that the rows which do join are a
fairly limited subset? Does the same advantage exist if filtering rows in
the joined table on some criteria, or is it better at that point to use an
inner join and add a where clause to filter the joined rows.

select table1.columns
from table1, table2
where table1.column = 'some_value'
and table1.fk = table2.pk
AND table2.column = 'some_other_value'

versus

select table1.columns
from table1
where table1.column = 'some_value'
and exists(select 1 from table2 where table1.fk = table2.pk
and table2.column ='some_other_value')