From: Sam Wong <sam@hellosam.net>
To: 'postgres performance list' <pgsql-performance@postgresql.org>
Subject: Re: Slow plan for MAX/MIN or LIMIT 1?
Date: Wed, 25 Sep 2013 20:23:04 +0800
Message-ID: <014b01ceb9e9$fc704de0$f550e9a0$@hellosam.net> (raw)
In-Reply-To: <CAHyXU0yt5xEgC-zZ3UmZqXhH2FKH9EJpQ8xC+=6VwLAjy4OSwg@mail.gmail.com>
References: <007801ceb907$d3706070$7a512150$@hellosam.net>
<CAHyXU0yt5xEgC-zZ3UmZqXhH2FKH9EJpQ8xC+=6VwLAjy4OSwg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sam@hellosam.net
Subject: Re: Slow plan for MAX/MIN or LIMIT 1?
In-Reply-To: <014b01ceb9e9$fc704de0$f550e9a0$@hellosam.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox