FAQ updated in section 4.8: My queries are slow or don't make use of the
indexes. Why?
is returned. In fact, though MAX() and MIN() don't use indexes, it is possible to retrieve such values using
anindex with ORDER BY and LIMIT:
<PRE> SELECT col FROM tab ORDER BY col LIMIT 1
</PRE>
---------------------------------------------------------------------------
Zeugswetter Andreas SB SD wrote:
>
> > The select(min) and select(max) took as long as the table scan to find
> > the count. It seems logical if a btree type index is available (such
> > as pk_cnx_ds_sis_bill_detl_tb) where the most significant bit of the
> > index is the column requested, it should be little more than a seek
> > first or seek last in the btree. Obviously, it won't work with a hashed
> > index (which is neither here nor there).
>
> In the meantime you can use:
> select extr_stu_id from cnx_ds_sis_bill_detl_tb order by 1 desc limit 1; -- max
> select extr_stu_id from cnx_ds_sis_bill_detl_tb order by 1 asc limit 1; -- min
>
> I guess that is the reason why nobody felt really motivated to implement
> this optimization. Besides these statements are more powerful, since they can fetch
> other columns from this min/max row. The down side is, that this syntax varies across
> db vendors, but most (all?) have a corresponding feature nowadays.
>
> select first 1
> select top 1 ...
>
> This is actually becoming a FAQ :-)
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
