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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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