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

select max(id) from aTable is very slow

From: David Teran <david(dot)teran(at)cluster9(dot)com>
To: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: select max(id) from aTable is very slow
Date: 2004-02-16 16:51:37
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

we have a table with about 6.000.000 rows. There is an index on a  
column with the name id which is an integer and serves as primary key.

When we execute select max(id) from theTable; it takes about 10  
seconds. Explain analyze returns:

  Aggregate  (cost=153635.15..153635.15 rows=1 width=4) (actual  
time=9738.263..9738.264 rows=1 loops=1)
    ->  Seq Scan on job_property  (cost=0.00..137667.32 rows=6387132  
width=4) (actual time=0.102..7303.649 rows=6387132 loops=1)
  Total runtime: 9738.362 ms
(3 rows)

I recreated the index on column id and ran vacuum analyze job_property  
but this did not help. I tried to force index usage with  SET  
ENABLE_SEQSCAN TO OFF; but the explain analyze still looks like the  
query is done using a seqscan.

Is the speed more or less normal for a 'dual G5 with 2 GHZ and 4 GB of  
Ram and a SATA hd' or do i miss something?

regards David


pgsql-performance by date

Next:From: Nick BarrDate: 2004-02-16 17:56:44
Subject: Re: select max(id) from aTable is very slow
Previous:From: Tom LaneDate: 2004-02-16 03:28:48
Subject: Re: optimization ideas for frequent, large(ish) updates

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