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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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