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

Hi Pavel,

Isn't the text for the Verbose Explain analyze not enough?
Is not, how can i generate it?

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