Re: Postgresql not using an index

2008/8/1 Marc Cuypers <m(dot)cuypers(at)mgvd(dot)be>:
> Hi Pavel,
>
> Isn't the text for the Verbose Explain analyze not enough?
> Is not, how can i generate it?
>
> --

no, I am missing statistics info

try
EXPLAIN ANALYZE SELECT .....

regards
Pavel Stehule
> 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
>>>
>