Обсуждение: 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