Skip site navigation (1) Skip section navigation (2)

Re: Query much faster with enable_seqscan=0

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ogden <lists(at)darkstatic(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query much faster with enable_seqscan=0
Date: 2010-09-21 23:30:32
Message-ID: 11723.1285111832@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Craig RingerDate: 2010-09-22 04:09:40
Subject: Re: Performance degradation, index bloat and planner estimates
Previous:From: Tom LaneDate: 2010-09-21 23:24:47
Subject: Re: Query much faster with enable_seqscan=0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group