Performance of MIN() and MAX()
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.
"Gene Sokolov" <hook@aktrad.ru> writes:
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.
You are mistaken. Postgres has no idea that min() and max() have any
semantics that have anything to do with indexes. I would like to see
that optimization myself, but it's not a particularly easy thing to add
given the system structure and the emphasis on datatype extensibility.
it's a show stopper for us.
You might be able to hack around the issue with queries like
SELECT x FROM table ORDER BY x LIMIT 1;
SELECT x FROM table ORDER BY x DESC LIMIT 1;
to get the min and max respectively. The current 6.6 code will
implement these with indexscans, although I think 6.5 would not
unless given an additional cue, like a WHERE clause involving x...
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofFri27Aug1999184547+0400084701bef09ad97ab5000d8cdac3@aktrad.ru | Resolved by subject fallback