On Fri, Mar 3, 2017 at 4:44 AM, Dinesh Chandra 12108 <Dinesh.Chandra@xxxxxxxxxx> wrote:
Dear Nur,
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
...
-> Index Scan using point_domain_class_id_index on point p (cost=0.00..1483472.70 rows=1454751 width=16) (actual time=27.265..142101.1
59 rows=1607491 loops=1)
Index Cond: (domain_class_id = 11)
Why wouldn't this be using a bitmap scan rather than a regular index scan? It seems like it should prefer the bitmap scan, unless the table is well clustered on domain_class_id. In which case, why isn't it just faster?
You could try repeating the explain analyze after setting enable_indexscan =off to see what that gives. If it gives a seq scan, then repeat with enable_seqscan also turned off. Or If it gives the bitmap scan, then repeat with enable_bitmapscan turned off.
How many rows is in point, and how big is it?
The best bet for making this better might be to have an index on (domain_class_id, modification_time) and hope for an index only scan. Except that you are on 9.1, so first you would have to upgrade. Which would allow you to use BUFFERS in the explain analyze, as well as track_io_timings, both of which would also be pretty nice to see. Using 9.1 is like having one hand tied behind your back.
Also, any idea why this execution of this query 15 is times faster than the execution you found in the log file? Was the top output you showed in the first email happening at the time the really slow query was running, or was that from a different period?
Cheers,
Jeff
