From: Andy <andy.gumbrecht@orprovision.com>
To: pgsql-performance@postgresql.org
Subject: Re: Speed of exist
Date: Tue, 19 Feb 2013 08:31:02 +0100
Message-ID: <51232A36.6000701@orprovision.com> (raw)
In-Reply-To: <51231D10.1070101@basenlily.me>
References: <51231D10.1070101@basenlily.me>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
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: andy.gumbrecht@orprovision.com
Subject: Re: Speed of exist
In-Reply-To: <51232A36.6000701@orprovision.com>
* 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