Re: Postgresql not using an index

2008/8/1 Marc Cuypers <m(dot)cuypers(at)mgvd(dot)be>:
> 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?

your statistics are absolutly out - planner expect 207K rows but
currently resault is one row - try to run ANALYZE statement or
increase your statistics
http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html
look ALTER TABLE SET STATISTICS

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