Performance of MIN() and MAX()

From: "Gene Sokolov" <hook(at)aktrad(dot)ru>
To: <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Performance of MIN() and MAX()
Date: 1999-08-27 14:45:47
Message-ID: 084701bef09a$d97ab500$0d8cdac3@aktrad.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

PGSQL 6.5.0, FreeBSD 3.2, Intel Pentium II 366MHz, 128 MB

The table below was filled with about 1,000,000 records. Then a bunch of
indexes was created and a VACUUM executed.

I was under impression that when max(<primary key>) is called, it should
just take the value from the index. I believe it should not do any kind of
scan. But, in fact, it scans the table.

select max(id) from ItemBars

takes well over 10 seconds to complete. It's almost instantaneous on MSSQL
or on Interbase. Something is clearly wrong. MAX() on the primary key should
not take so much time. Am I doing something wrong? Is it a known bug? If
it's a bug then it's a show stopper for us. How can I help fixing it?

CREATE TABLE ItemBars (
ID SERIAL PRIMARY KEY ,
ItemID INT NOT NULL ,
Interv INT NOT NULL ,
StaTS DATETIME NOT NULL ,
EndTS DATETIME NOT NULL ,
IsActive BOOL NOT NULL ,
Opn FLOAT(7) NOT NULL ,
High FLOAT(7) NOT NULL ,
Low FLOAT(7) NOT NULL ,
Cls FLOAT(7) NOT NULL ,
Vol INT NOT NULL
);

Gene Sokolov.

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 1999-08-27 15:29:44 Re: [HACKERS] Performance of MIN() and MAX()
Previous Message Tom Lane 1999-08-27 13:58:08 Re: [HACKERS] ERROR: pull_var_clause: Cannot handle node type 108