Re: Postgresql not using an index

Hi Pavel,

Pavel Stehule schreef:
> 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

I entered the command in pgsql and got the following output:

# explain analyze select * from file where jobid=2792;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=37738.780..90453.299 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 90453.419 ms
(3 rows)

Makes any sence?

--
Marc

>>
>> 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
>>>>
>