Thread: Postgresql not using an index
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
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
>
Marc --
> 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
Perhaps jobid is not an int -- maybe a bigint ? In which case the types don't match and the index won't be used (newer versions might do ok but 7.4 won't IIRC).
Perhaps we could see the table description from \d in the psql tool ?
HTH,
Greg Williamson
Senior DBA
DigitalGlobe
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
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
>>
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
>>>
>
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
>>>>
>
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 >>>>> >> >