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
