From: Bastiaan Olij <bastiaan@basenlily.me>
To: pgsql-performance@postgresql.org
Subject: Re: Speed of exist
Date: Tue, 19 Feb 2013 18:36:48 +1100
Message-ID: <51232B90.2080504@basenlily.me> (raw)
In-Reply-To: <51232A36.6000701@orprovision.com>
References: <51231D10.1070101@basenlily.me>
<51232A36.6000701@orprovision.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Andy,
I've tried that with the same result. One subquery works beautifully,
two subqueries with an OR and it starts to do a sequential scan...
Thanks,
Bastiaan Olij
On 19/02/13 6:31 PM, Andy wrote:
> Limit the sub-queries to 1, i.e. :
>
> select 1 from Table2 where Table2.ForeignKey = Table1.PrimaryKey fetch
> first 1 rows only
>
> Andy.
>
> On 19.02.2013 07:34, Bastiaan Olij wrote:
>> Hi All,
>>
>> Hope someone can help me a little bit here:
>>
>> I've got a query like the following:
>> --
>> select Column1, Column2, Column3
>> from Table1
>> where exists (select 1 from Table2 where Table2.ForeignKey =
>> Table1.PrimaryKey)
>> or exists (select 1 from Table3 where Table3.ForeignKey =
>> Table1.PrimaryKey)
>> --
>>
>> Looking at the query plan it is doing a sequential scan on both Table2
>> and Table3.
>>
>> If I remove one of the subqueries and turn the query into:
>> --
>> select Column1, Column2, Column3
>> from Table1
>> where exists (select 1 from Table2 where Table2.ForeignKey =
>> Table1.PrimaryKey)
>> --
>>
>> It is nicely doing an index scan on the index that is on
>> Table2.ForeignKey.
>>
>> As Table2 and Table3 are rather large the first query takes minutes
>> while the second query takes 18ms.
>>
>> Is there a way to speed this up or an alternative way of selecting
>> records from Table1 which have related records in Table2 or Table3 which
>> is faster?
>>
>> Kindest Regards,
>>
>> Bastiaan Olij
>>
>>
>>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bastiaan@basenlily.me
Subject: Re: Speed of exist
In-Reply-To: <51232B90.2080504@basenlily.me>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox