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