From: | Francisco Reyes <lists(at)stringsutils(dot)com> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Select max(primary_key) taking a long time |
Date: | 2010-05-21 15:01:21 |
Message-ID: | cone.1274454081.672731.11718.1000@shelca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Postgres 8.4.1
CentOS 5.4
I am trying to do
select max(primary_key) from some_table;
The explain looks like:
explain select max(primary_key) from some_table;
QUERY PLAN
----------------------------------------------------------------------------
Result (cost=0.15..0.16 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.15 rows=1 width=8)
-> Index Scan Backward using some_table_pkey on some_table
(cost=0.00..161797059.16 rows=1086279613 width=8)
Filter: (trans_id IS NOT NULL)
I checked there wasn't a lock of any kind
select waiting from pg_stat_activity;
waiting
---------
f
f
f
f
f
f
f
(7 rows)
IOwait on the machine is around 20%
sar 10 5
Linux 2.6.18-128.el5 (trans05.afs) 05/21/2010
10:56:49 AM CPU %user %nice %system %iowait %steal %idle
10:56:59 AM all 5.90 0.00 2.04 20.67 0.00 71.39
10:57:09 AM all 5.90 0.00 1.99 23.36 0.00 68.75
10:57:19 AM all 5.87 0.00 2.10 22.56 0.00 69.47
10:57:29 AM all 5.84 0.00 2.09 23.56 0.00 68.51
10:57:39 AM all 6.30 0.00 2.23 21.53 0.00 69.94
Average: all 5.96 0.00 2.09 22.34 0.00 69.61
Any ideas why the select would be taking long.. It has gone on for minutes
with no answer. I can just look at the value of the sequence for the primary
key, but I am curious why something that usually is sub-second is taking so
long..
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-05-21 15:27:41 | Re: Select max(primary_key) taking a long time |
Previous Message | Dino Vliet | 2010-05-21 14:35:57 | Re: last and/or first in a by group |