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

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?

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