Postgresql not using an index - Mailing list pgsql-general

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