Re: Postgresql not using an index - Mailing list pgsql-general

2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
> Hi Pavel,
>
> Pavel Stehule schreef:
>>
>> 2008/8/1 Marc Cuypers <m.cuypers@mgvd.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.cuypers@mgvd.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@postgresql.org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>>
>>
>