From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
Cc: Nur Agus <nuragus.linux@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance issue in PostgreSQL server...
Date: Fri, 03 Mar 2017 10:19:48 -0500
Message-ID: <18838.1488554388@sss.pgh.pa.us> (raw)
In-Reply-To: <b513b675b75b452182628ca2667bf679@cyient.com>
References: <0796d1d53d3e4aa692cfdc329f86b399@cyient.com>
<CAM7u+C7vUQ=U558JcGz83Sq2Z+wE2FEsup1hxBsmG5t3Kn08hw@mail.gmail.com>
<b513b675b75b452182628ca2667bf679@cyient.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com> writes:
> The below is the output for psql=> EXPLAIN ANALYZE SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN evidence.observation_evidence oe ON p.feature_id = oe.evd_feature_id WHERE p.domain_class_id IN (11) AND (p.modification_time > '2015-05-10 00:06:56.056 IST' OR oe.modification_time > '2015-05-10 00:06:56.056 IST') ORDER BY feature_id
I think that's a fundamentally slow query and you're not going to be able
to make it better without rethinking your requirements and/or data
representation. As written, that requires the server to form the entire
join of p to oe on feature_id, with the only filter before the join being
the evidently-none-too-selective domain_class_id condition. Only after
joining can it apply the OR condition. So this is inherently processing a
lot of rows.
If the OR arms were individually pretty selective you could rewrite this
into a UNION of two joins, a la the discussion at
https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05c-f0b4-2fdfc8873489@BlueTreble.com
but given the dates involved I'm betting that won't help very much.
Or maybe you could try
select feature_id from p where domain_class_id IN (11) AND p.modification_time > '2015-05-10 00:06:56.056 IST'
intersect
select feature_id from oe where oe.modification_time > '2015-05-10 00:06:56.056 IST'
order by feature_id
although I'm not entirely certain that that has exactly the same
semantics (-ENOCAFFEINE), and it might still be none too quick.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, Dinesh.Chandra@cyient.com, nuragus.linux@gmail.com
Subject: Re: Performance issue in PostgreSQL server...
In-Reply-To: <18838.1488554388@sss.pgh.pa.us>
* 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