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

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