Re: Postgresql not using an index

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
>