From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Dinesh Chandra 12108 <Dinesh(dot)Chandra(at)cyient(dot)com> |
Cc: | Nur Agus <nuragus(dot)linux(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Performance issue in PostgreSQL server... |
Date: | 2017-03-03 15:19:48 |
Message-ID: | 18838.1488554388@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Dinesh Chandra 12108 <Dinesh(dot)Chandra(at)cyient(dot)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(at)BlueTreble(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2017-03-06 04:23:08 | Re: Performance issue in PostgreSQL server... |
Previous Message | Merlin Moncure | 2017-03-03 15:03:04 | Re: Suggestions for a HBA controller (6 x SSDs + madam RAID10) |