From: Bastiaan Olij <bastiaan@basenlily.me>
To: Postgres performance mailing list <pgsql-performance@postgresql.org>
Subject: Speed of exist
Date: Tue, 19 Feb 2013 17:34:56 +1100
Message-ID: <51231D10.1070101@basenlily.me> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
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: <51231D10.1070101@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