Hi All and Merlin, So here is the explain analyze output. ------ Query A -- single row output, but very slow query ------ SELECT min(log_id) FROM event_log WHERE event='S-Create' AND insert_time>'2013-09-15' and insert_time<'2013-09-16' http://explain.depesz.com/s/3H5 Result (cost=134.48..134.49 rows=1 width=0) (actual time=348370.719..348370.720 rows=1 loops=1) Output: $0 InitPlan 1 (returns $0) -> Limit (cost=0.00..134.48 rows=1 width=8) (actual time=348370.712..348370.713 rows=1 loops=1) Output: uco.event_log.log_id -> Index Scan using event_log_pkey on uco.event_log (cost=0.00..1525564.02 rows=11344 width=8) (actual time=348370.709..348370.709 rows=1 loops=1) Output: uco.event_log.log_id Index Cond: (uco.event_log.log_id IS NOT NULL) Filter: ((uco.event_log.insert_time > '2013-09-15 00:00:00'::timestamp without time zone) AND (uco.event_log.insert_time < '2013-09-16 00:00:00'::timestamp without time zone) AND (uco.event_log.event = 'S-Create'::text)) Rows Removed by Filter: 43249789 Total runtime: 348370.762 ms ------ Query B -- multiple row output, fast query, but I could get what I want from the first output row ------ SELECT log_id FROM event_log WHERE event='S-Create' AND insert_time>'2013-09-15' and insert_time<'2013-09-16' ORDER BY log_id http://explain.depesz.com/s/s6P Sort (cost=41015.85..41021.52 rows=11344 width=8) (actual time=3651.695..3652.160 rows=6948 loops=1) Output: log_id Sort Key: event_log.log_id Sort Method: quicksort Memory: 518kB -> Bitmap Heap Scan on uco.event_log (cost=311.42..40863.05 rows=11344 width=8) (actual time=448.349..3645.465 rows=6948 loops=1) Output: log_id Recheck Cond: ((event_log.event = 'S-Create'::text) AND (event_log.insert_time > '2013-09-15 00:00:00'::timestamp without time zone) AND (event_log.insert_time < '2013-09-16 00:00:00'::timestamp without time zone)) -> Bitmap Index Scan on event_data_search (cost=0.00..310.86 rows=11344 width=0) (actual time=447.670..447.670 rows=6948 loops=1) Index Cond: ((event_log.event = 'S-Create'::text) AND (event_log.insert_time > '2013-09-15 00:00:00'::timestamp without time zone) AND (event_log.insert_time < '2013-09-16 00:00:00'::timestamp without time zone)) Total runtime: 3652.535 ms P.S. If I put a LIMIT 1 at the end of this query, it will get an identical plan just like Query A. ------ My observation: In Query A, the lower bound of the INDEX SCAN node estimation is way off. It won't get the first row output right at 0.00 because the filter needed to be applied. Thanks, Sam -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
