Re: Slow plan for MAX/MIN or LIMIT 1? - Mailing list pgsql-performance

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