Thread: Astonishing explain...!

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

- Mike H.

mvh=> explain verbose select max(num) from boogers;
NOTICE:  QUERY DUMP:

{ AGG :startup_cost 49665.54 :total_cost 49665.54 :rows 1 :width 4 :qptargetlist
 ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname max
 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggna
me max :basetype 23 :aggtype 23 :target { VAR :varno 0 :varattno 1 :vartype 23 :
vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} :aggstar false :aggdistin
ct false }}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00 :total_cost 43814
.43 :rows 2340443 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno
1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :v
arlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :
extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 } :righttree <> :extprm
() :locprm () :initplan <> :nprm 0 }
NOTICE:  QUERY PLAN:

Aggregate  (cost=49665.54..49665.54 rows=1 width=4)
  ->  Seq Scan on boogers  (cost=0.00..43814.43 rows=2340443 width=4)

On Mon, 24 Jun 2002 08:43:16 -0700 (PDT)
"Mike Harding" <mvh@ix.netcom.com> wrote:
> Why is a sequential scan be done for MAX of an indexed column?  This
> is obviously pretty painful for large tables...

This is an FAQ -- check the archives for much more discussion of it.
In short, the workaround is to create a btree index on the column
in question, and do:

SELECT num FROM boogers ORDER BY num LIMIT 1;

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

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

Neil Conway wrote:
> On Mon, 24 Jun 2002 08:43:16 -0700 (PDT)
> "Mike Harding" <mvh@ix.netcom.com> wrote:
> > Why is a sequential scan be done for MAX of an indexed column?  This
> > is obviously pretty painful for large tables...
>
> This is an FAQ -- check the archives for much more discussion of it.

Well, an FAQ item as of yesterday anyway.  ;-)


> In short, the workaround is to create a btree index on the column
> in question, and do:
>
> SELECT num FROM boogers ORDER BY num LIMIT 1;
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>

--
  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, Pennsylvania 19026