Postgresql not using an index

Started by Marc Cuypersover 17 years ago7 messagesgeneral

m.cuypers@mgvd.be

over 17 years ago

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

Pavel Stehule

pavel.stehule@gmail.com

over 17 years ago

In reply to: Marc Cuypers (#1)

Re: Postgresql not using an index

Hello

please, send EXPLAIN ANALYZE output.

regards
Pavel Stehule

2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:

Show quoted text

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

Gregory Williamson

Gregory.Williamson@digitalglobe.com

over 17 years ago

In reply to: Marc Cuypers (#1)

Re: Postgresql not using an index

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

Marc Cuypers

m.cuypers@mgvd.be

over 17 years ago

In reply to: Pavel Stehule (#2)

Re: Postgresql not using an index

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:

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

over 17 years ago

In reply to: Marc Cuypers (#4)

Re: Postgresql not using an index

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

Show quoted text

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

Marc Cuypers

m.cuypers@mgvd.be

over 17 years ago

In reply to: Pavel Stehule (#5)

Re: Postgresql not using an index

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

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

over 17 years ago

In reply to: Marc Cuypers (#6)

Re: Postgresql not using an index

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

Show quoted text

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