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)
