Hi Pavel, Isn't the text for the Verbose Explain analyze not enough? Is not, how can i generate it? -- Best regards, 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
