Postgresql not using an index

[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