Re: Postgresql not using an index

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(dot)cuypers(at)mgvd(dot)be>:
>> 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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>