Re: Astonishing explain...!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I did the following explain, and was suprised by the results (yes,
> vacuum, analyze, etc.)
>
> Why is a sequential scan be done for MAX of an indexed column?  This
> is obviously pretty painful for large tables...

Painful, but still the way that Postgres does it. An alternative is
to do this:

SELECT num FROM boogers ORDER BY num DESC LIMIT 1;

I've heard talk of this being fixed^H^H^H^H^Hoptimized someday
(7.3 hopefully?) but for now the above SQL is your best bet:

test=# EXPLAIN SELECT num FROM boogers ORDER BY num DESC LIMIT 1;

          QUERY PLAN
- -----------------------------------------------------
 Limit  (cost=0.00..0.04 rows=1 width=4)
   ->  Index Scan Backward using nose on boogers


Greg Sabino Mullane  greg@turnstep.com
PGP Key: 0x14964AC8 200206242029

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE9F7t8vJuQZxSWSsgRAhqBAJ4xjJgO+MHr/uwEwEXRxQIuFIEyOwCg/O28
gMugobeu//Q4gBKX2H/Lknk=
=sIYv
-----END PGP SIGNATURE-----