Re: Speed of exist

2013/2/19 Bastiaan Olij <bastiaan(at)basenlily(dot)me>:
> 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...

try to rewrite OR to two SELECTs joined by UNION ALL

Pavel

>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance