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

Tom Lane wrote in message <16225(dot)937319132(at)sss(dot)pgh(dot)pa(dot)us>...

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

I did some testing today on a 1.6 million row table of random integers
in the range of 0..32767. Using explain I could get a "select max(f1)..."
down to a cost of about 30000 using a where clause of "f1 > 0"...

After running the queries I achieved the following results (dual P133,
w/ 128 megs ram, IDE)...

select max(f1) from t1 [68 seconds] [explain cost 60644.00]
select max(f1) from t1 where f1 > 0 [148 seconds] [explain cost
30416.67]

Knowing my data does have at least one value above 30000 I can apply a
better heuristic other than f1 > 0

select max(f1) from t1 where f1 > 30000 [12.43 seconds] [explain cost
30416.67]

Until more agg. function optimizations are implemented, programmers
might have to use the old melon to speed things up.

Damond