Message-Id: <6.2.1.2.0.20060120120150.08ab7b00@localhost>
Date: Fri, 20 Jan 2006 12:35:36 +0800
To: pgsql-performance@postgresql.org
From: K C Lau <kclau60@netvigator.com>
Subject: SELECT MIN, MAX took longer time than SELECT COUNT, MIN, MAX
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed


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