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-----
Import Notes
Resolved by subject fallback
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