Dinesh Chandra 12108 <dinesh.chan...@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-2fdfc8873...@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
