Skip site navigation (1) Skip section navigation (2)

select max() much slower than select min()

From: Brian Cox <brian(dot)cox(at)ca(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: select max() much slower than select min()
Date: 2009-06-18 23:34:39
Message-ID: 4A3ACF0F.3080701@ca.com (view raw or flat)
Thread:
Lists: pgsql-performance
ts_stats_transet_user_interval has ~48M rows. ts_id is the PK and there 
is an index on ts_interval_start_time. I reindexed it and ran vacuum 
analyze. Only SELECTs have been done since these operations.

cemdb=# explain select min(ts_id) from ts_stats_transet_user_interval a 
where 0=0 and a.ts_interval_start_time >= '2009-6-16 01:00' and 
a.ts_interval_start_time < '2009-6-16 02:00';
 
                               QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Result  (cost=12.19..12.20 rows=1 width=0)
    InitPlan
      ->  Limit  (cost=0.00..12.19 rows=1 width=8)
            ->  Index Scan using ts_stats_transet_user_interval_pkey on 
ts_stats_transet_user_interval a  (cost=0.00..5496152.30 rows=450799 
width=8)
                  Filter: ((ts_id IS NOT NULL) AND 
(ts_interval_start_time >= '2009-06-16 01:00:00-07'::timestamp with time 
zone) AND (ts_interval_start_time < '2009-06-16 02:00:00-07'::timestamp 
with time zone))
(5 rows)
cemdb=# explain select max(ts_id) from ts_stats_transet_user_interval a 
where 0=0 and a.ts_interval_start_time >= '2009-6-16 01:00' and 
a.ts_interval_start_time < '2009-6-16 02:00';
 
                               QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Result  (cost=12.19..12.20 rows=1 width=0)
    InitPlan
      ->  Limit  (cost=0.00..12.19 rows=1 width=8)
            ->  Index Scan Backward using 
ts_stats_transet_user_interval_pkey on ts_stats_transet_user_interval a 
  (cost=0.00..5496152.30 rows=450799 width=8)
                  Filter: ((ts_id IS NOT NULL) AND 
(ts_interval_start_time >= '2009-06-16 01:00:00-07'::timestamp with time 
zone) AND (ts_interval_start_time < '2009-06-16 02:00:00-07'::timestamp 
with time zone))
(5 rows)
[root(at)rdl64xeoserv01 log]# time PGPASSWORD=quality psql -U admin -d 
cemdb -c "select min(ts_id) from ts_stats_transet_user_interval a where 
a.ts_interval_start_time >= '2009-6-16 01:00' and 
a.ts_interval_start_time < '2009-6-16 02:00'"         min
--------------------
  600000000032100000
(1 row)


real    1m32.025s
user    0m0.000s
sys     0m0.003s
[root(at)rdl64xeoserv01 log]# time PGPASSWORD=quality psql -U admin -d 
cemdb -c "select max(ts_id) from ts_stats_transet_user_interval a where 
a.ts_interval_start_time >= '2009-6-16 01:00' and 
a.ts_interval_start_time < '2009-6-16 02:00'"
         max
--------------------
  600000000032399999
(1 row)


real    16m39.412s
user    0m0.002s
sys     0m0.002s


seems like max() shouldn't take any longer than min() and certainly not 
10 times as long. Any ideas on how to determine the max more quickly?

Thanks,
Brian

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-06-19 00:15:43
Subject: Re: select max() much slower than select min()
Previous:From: Bryce EwingDate: 2009-06-18 22:11:25
Subject: Re: Index Scan taking long time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group