Re: [HACKERS] Performance of MIN() and MAX()

From: Tom Lane <tgl@sss.pgh.pa.us>
> > 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;

It is a real show stopper. No luck completely, the indexes are ignored:

*************************************************************
[PostgreSQL 6.5.0 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1]

bars=> create index bars_id on itemsbars(id);
CREATE
bars=> explain select id from itemsbars order by id limit 1;
NOTICE:  QUERY PLAN:

Sort  (cost=44404.41 rows=969073 width=4) ->  Seq Scan on itemsbars  (cost=44404.41 rows=969073 width=4)

EXPLAIN
bars=> \d itemsbars
Table    = itemsbars
+--------------------+----------------------------------+-------+
|       Field        |              Type                | Length|
+--------------------+----------------------------------+-------+
| id                 | int4 not null default nextval('" |     4 |
| itemid             | int4 not null                    |     4 |
| interv             | int4 not null                    |     4 |
| stats              | datetime not null                |     8 |
| endts              | datetime not null                |     8 |
| isactive           | bool not null                    |     1 |
| opn                | float8 not null                  |     8 |
| high               | float8 not null                  |     8 |
| low                | float8 not null                  |     8 |
| cls                | float8 not null                  |     8 |
| vol                | int4 not null                    |     4 |
+--------------------+----------------------------------+-------+
Indices:  bars_complex2         bars_endts         bars_id         bars_interv         bars_itemid         bars_stats
     itemsbars_pkey