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

"Gene Sokolov" <hook(at)aktrad(dot)ru> writes:
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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:

> 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)

Yes, you missed my comment that 6.5.* needs some help or it won't
consider an index scan at all. This gives the right sort of plan:

regression=> explain select id from itemsbars where id > 0 order by id limit 1;
NOTICE: QUERY PLAN:
Index Scan using itemsbars_id_key on itemsbars (cost=21.67 rows=334 width=4)

The WHERE clause can be chosen so that it won't actually exclude
anything, but there has to be a WHERE clause that looks useful with
an index or an indexscan plan won't even get generated. (Also,
the DESC case doesn't work in 6.5.*, unless you apply the backwards-
index-scan patch that Hiroshi posted a few weeks ago.)

This is fixed in current sources, BTW:

regression=> explain select id from itemsbars order by id limit 1;
NOTICE: QUERY PLAN:
Index Scan using bars_id on itemsbars (cost=62.00 rows=1000 width=4)
regression=> explain select id from itemsbars order by id desc ;
NOTICE: QUERY PLAN:
Index Scan Backward using bars_id on itemsbars (cost=62.00 rows=1000 width=4)

although we still need to do some rejiggering of the cost estimation
rules; current sources are probably *too* eager to use an indexscan.

regards, tom lane