pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSpeed of exist
5+ messages / 3 participants
[nested] [flat]
* Speed of exist
@ 2013-02-19 06:34 Bastiaan Olij <bastiaan@basenlily.me>
0 siblings, 1 reply; 5+ messages in thread
From: Bastiaan Olij @ 2013-02-19 06:34 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed of exist
@ 2013-02-19 07:31 Andy <andy.gumbrecht@orprovision.com>
parent: Bastiaan Olij <bastiaan@basenlily.me>
0 siblings, 1 reply; 5+ messages in thread
From: Andy @ 2013-02-19 07:31 UTC (permalink / raw)
To: pgsql-performance
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
>
>
>
--
------------------------------------------------------------------------------------------------------------------------
*Andy Gumbrecht*
Research & Development
Orpro Vision GmbH
Hefehof 24, 31785, Hameln
+49 (0) 5151 809 44 21
+49 (0) 1704 305 671
andy.gumbrecht@orprovision.com
www.orprovision.com
Orpro Vision GmbH
Sitz der Gesellschaft: 31785, Hameln
USt-Id-Nr: DE264453214
Amtsgericht Hannover HRB204336
Geschaeftsfuehrer: Roberto Gatti, Massimo Gatti, Adam Shaw
------------------------------------------------------------------------------------------------------------------------
Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige
Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und
vernichten Sie diese Mail. Das unerlaubte Kopieren, jegliche anderweitige Verwendung sowie die unbefugte
Weitergabe dieser Mail ist nicht gestattet.
------------------------------------------------------------------------------------------------------------------------
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient
(or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any
unauthorized copying, disclosure, distribution or other use of the material or parts thereof is strictly
forbidden.
------------------------------------------------------------------------------------------------------------------------
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed of exist
@ 2013-02-19 07:36 Bastiaan Olij <bastiaan@basenlily.me>
parent: Andy <andy.gumbrecht@orprovision.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bastiaan Olij @ 2013-02-19 07:36 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed of exist
@ 2013-02-19 07:39 Pavel Stehule <pavel.stehule@gmail.com>
parent: Bastiaan Olij <bastiaan@basenlily.me>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2013-02-19 07:39 UTC (permalink / raw)
To: Bastiaan Olij <bastiaan@basenlily.me>; +Cc: pgsql-performance
2013/2/19 Bastiaan Olij <bastiaan@basenlily.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Speed of exist
@ 2013-02-19 10:18 Bastiaan Olij <bastiaan@basenlily.me>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bastiaan Olij @ 2013-02-19 10:18 UTC (permalink / raw)
To: pgsql-performance
Hi Pavel,
That is what I've done in this particular case but there are parts where
I use exist checks in this way that are very cumbersome to write out
like that so I'm hoping there is a way to make the optimizer work with
existence checks in this way.
Cheers,
Bastiaan Olij
On 19/02/13 6:39 PM, Pavel Stehule wrote:
> 2013/2/19 Bastiaan Olij <bastiaan@basenlily.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
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-02-19 10:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-19 06:34 Speed of exist Bastiaan Olij <bastiaan@basenlily.me>
2013-02-19 07:31 ` Andy <andy.gumbrecht@orprovision.com>
2013-02-19 07:36 ` Bastiaan Olij <bastiaan@basenlily.me>
2013-02-19 07:39 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-02-19 10:18 ` Bastiaan Olij <bastiaan@basenlily.me>
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