2008/8/1 Marc Cuypers <m.cuypers@xxxxxxx>: > Hi Pavel, > > Pavel Stehule schreef: >> >> 2008/8/1 Marc Cuypers <m.cuypers@xxxxxxx>: >>> >>> Hi Pavel, >>> >>> Isn't the text for the Verbose Explain analyze not enough? >>> Is not, how can i generate it? >>> >>> -- >> >> >> no, I am missing statistics info >> >> try >> EXPLAIN ANALYZE SELECT ..... >> >> regards > > I entered the command in pgsql and got the following output: > > # explain analyze select * from file where jobid=2792; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------ > Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual > time=37738.780..90453.299 rows=1 loops=1) > Filter: (jobid = 2792) > Total runtime: 90453.419 ms > (3 rows) > > Makes any sence? your statistics are absolutly out - planner expect 207K rows but currently resault is one row - try to run ANALYZE statement or increase your statistics http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html look ALTER TABLE SET STATISTICS regards Pavel > > -- > Marc > >>> >>> Marc >>> >>> Pavel Stehule schreef: >>>> >>>> Hello >>>> >>>> please, send EXPLAIN ANALYZE output. >>>> >>>> regards >>>> Pavel Stehule >>>> >>>> 2008/8/1 Marc Cuypers <m.cuypers@xxxxxxx>: >>>>> >>>>> Hi, >>>>> >>>>> I'm using postgres 7.4 and bacula 1.38 on debian. >>>>> >>>>> In the bacula database there is a table named 'file' which has about >>>>> 2.5 >>>>> million rows. >>>>> In this table there is a field 'jobid' which is indexed. >>>>> The index is created with the following command: >>>>> CREATE INDEX file_jobid_idx ON file USING btree (jobid); >>>>> >>>>> The query: >>>>> SELECT * from file where jobid=2792 >>>>> >>>>> does a full scan and to my opinion it doesn't use the index. >>>>> I already did a VACUUM ANALYZE on the database. >>>>> >>>>> >>>>> Somebody an idea? >>>>> >>>>> EXPLAIN tells the following: >>>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual >>>>> time=103215.145..161153.664 rows=1 loops=1) >>>>> Filter: (jobid = 2792) >>>>> Total runtime: 161154.734 ms >>>>> >>>>> The Verbose Explain tells the following: >>>>> {SEQSCAN >>>>> :startup_cost 0.00 >>>>> :total_cost 707683.30 >>>>> :plan_rows 207562 >>>>> :plan_width 110 >>>>> :targetlist ( >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 1 >>>>> :restype 23 >>>>> :restypmod -1 >>>>> :resname fileid >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 1 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 1 >>>>> :vartype 23 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 1 >>>>> } >>>>> } >>>>> >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 2 >>>>> :restype 23 >>>>> :restypmod -1 >>>>> :resname fileindex >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 2 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 2 >>>>> :vartype 23 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 2 >>>>> } >>>>> } >>>>> >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 3 >>>>> :restype 23 >>>>> :restypmod -1 >>>>> :resname jobid >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 3 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 3 >>>>> :vartype 23 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 3 >>>>> } >>>>> } >>>>> >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 4 >>>>> :restype 23 >>>>> :restypmod -1 >>>>> :resname pathid >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 4 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 4 >>>>> :vartype 23 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 4 >>>>> } >>>>> } >>>>> >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 5 >>>>> :restype 23 >>>>> :restypmod -1 >>>>> :resname filenameid >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 5 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 5 >>>>> :vartype 23 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 5 >>>>> } >>>>> } >>>>> >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 6 >>>>> :restype 23 >>>>> :restypmod -1 >>>>> :resname markid >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 6 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 6 >>>>> :vartype 23 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 6 >>>>> } >>>>> } >>>>> >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 7 >>>>> :restype 25 >>>>> :restypmod -1 >>>>> :resname lstat >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 7 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 7 >>>>> :vartype 25 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 7 >>>>> } >>>>> } >>>>> >>>>> {TARGETENTRY >>>>> :resdom >>>>> {RESDOM >>>>> :resno 8 >>>>> :restype 25 >>>>> :restypmod -1 >>>>> :resname md5 >>>>> :ressortgroupref 0 >>>>> :resorigtbl 440806231 >>>>> :resorigcol 8 >>>>> :resjunk false >>>>> } >>>>> >>>>> :expr >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 8 >>>>> :vartype 25 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 8 >>>>> } >>>>> } >>>>> ) >>>>> >>>>> :qual ( >>>>> {OPEXPR >>>>> :opno 96 >>>>> :opfuncid 65 >>>>> :opresulttype 16 >>>>> :opretset false >>>>> :args ( >>>>> {VAR >>>>> :varno 1 >>>>> :varattno 3 >>>>> :vartype 23 >>>>> :vartypmod -1 >>>>> :varlevelsup 0 >>>>> :varnoold 1 >>>>> :varoattno 3 >>>>> } >>>>> >>>>> {CONST >>>>> :consttype 23 >>>>> :constlen 4 >>>>> :constbyval true >>>>> :constisnull false >>>>> :constvalue 4 [ -24 10 0 0 ] >>>>> } >>>>> ) >>>>> } >>>>> ) >>>>> >>>>> :lefttree <> >>>>> :righttree <> >>>>> :initPlan <> >>>>> :extParam () >>>>> >>>>> :allParam () >>>>> >>>>> :nParamExec 0 >>>>> :scanrelid 1 >>>>> } >>>>> >>>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual >>>>> time=103215.145..161153.664 rows=1 loops=1) >>>>> Filter: (jobid = 2792) >>>>> Total runtime: 161154.734 ms >>>>> >>>>> >>>>> -- >>>>> Best regards, >>>>> >>>>> Marc >>>>> >>>>> -- >>>>> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) >>>>> To make changes to your subscription: >>>>> http://www.postgresql.org/mailpref/pgsql-general >>>>> >> >
