[GENERAL] Postgresql not using an index

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general