From: K C Lau <kclau60@netvigator.com>
To: pgsql-performance@postgresql.org
Subject: SELECT MIN, MAX took longer time than SELECT COUNT, MIN, MAX
Date: Fri, 20 Jan 2006 12:35:36 +0800
Message-ID: <6.2.1.2.0.20060120120150.08ab7b00@localhost> (raw)
The following query took 17 seconds:
select count(LogSN), min(LogSN), max(LogSN) from Log where create_time <
'2005/10/19';
Figuring that getting the count will involve scanning the database, I took
it out, but the new query took 200 seconds:
select min(LogSN), max(LogSN) from Log where create_time < '2005/10/19';
Is it because the planner is using index pk_log instead of idx_logtime?
Anyway to avoid that?
I can get instant replies with 2 separate queries for min(LogSN) and
max(LogSN) using order by create_time limit 1, but I can't get both values
within 1 query using the limit 1 construct. Any suggestions?
I am running pg 8.1.2 on Windows 2000. The queries are done immediately
after a vacuum analyze.
Best regards,
KC.
----------------------
esdt=> \d log;
create_time | character varying(23) | default
'1970/01/01~00:00:00.000'::char
acter varying
logsn | integer | not null
...
Indexes:
"pk_log" PRIMARY KEY, btree (logsn)
"idx_logtime" btree (create_time, logsn)
...
esdt=> vacuum analyze log;
VACUUM
esdt=> explain analyze select count(LogSN), min(LogSN), max(LogSN) from Log
where create_time < '2005/10/19';
Aggregate (cost=57817.74..57817.75 rows=1 width=4) (actual
time=17403.381..17403.384 rows=1 loops=1)
-> Bitmap Heap Scan on log (cost=1458.31..57172.06 rows=86089
width=4) (actual time=180.368..17039.262 rows=106708 loops=1)
Recheck Cond: ((create_time)::text < '2005/10/19'::text)
-> Bitmap Index Scan on idx_logtime (cost=0.00..1458.31
rows=86089 width=0) (actual time=168.777..168.777 rows=106708 loops=1)
Index Cond: ((create_time)::text < '2005/10/19'::text)
Total runtime: 17403.787 ms
esdt=> explain analyze select min(LogSN), max(LogSN) from Log where
create_time < '2005/10/19';
Result (cost=2.51..2.52 rows=1 width=0) (actual
time=200051.507..200051.510 rows=1 loops=1)
InitPlan
-> Limit (cost=0.00..1.26 rows=1 width=4) (actual
time=18.541..18.544 rows=1 loops=1)
-> Index Scan using pk_log on log (cost=0.00..108047.11
rows=86089
width=4) (actual time=18.533..18.533 rows=1 loops=1)
Filter: (((create_time)::text < '2005/10/19'::text) AND
(logsn IS NOT NULL))
-> Limit (cost=0.00..1.26 rows=1 width=4) (actual
time=200032.928..200032.931 rows=1 loops=1)
-> Index Scan Backward using pk_log on
log (cost=0.00..108047.11 rows=86089 width=4) (actual
time=200032.920..200032.920 rows=1 loops=1)
Filter: (((create_time)::text < '2005/10/19'::text) AND
(logsn IS NOT NULL))
Total runtime: 200051.701 ms
esdt=> explain analyze select LogSN from Log where create_time <
'2005/10/19' order by create_time limit 1;
Limit (cost=0.00..0.98 rows=1 width=31) (actual time=0.071..0.073 rows=1
loops=1)
-> Index Scan using idx_logtime on log (cost=0.00..84649.94
rows=86089 width=31) (actual time=0.063..0.063 rows=1 loops=1)
Index Cond: ((create_time)::text < '2005/10/19'::text)
Total runtime: 0.182 ms
esdt=> explain analyze select LogSN from Log where create_time <
'2005/10/19' order by create_time desc limit 1;
Limit (cost=0.00..0.98 rows=1 width=31) (actual time=0.058..0.061 rows=1
loops=1)
-> Index Scan Backward using idx_logtime on log (cost=0.00..84649.94
rows=86089 width=31) (actual time=0.051..0.051 rows=1 loops=1)
Index Cond: ((create_time)::text < '2005/10/19'::text)
Total runtime: 0.186 ms
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: kclau60@netvigator.com
Subject: Re: SELECT MIN, MAX took longer time than SELECT COUNT, MIN, MAX
In-Reply-To: <6.2.1.2.0.20060120120150.08ab7b00@localhost>
* 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