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