Hi Pavel,
Pavel Stehule schreef:
2008/8/1 Marc Cuypers <m.cuypers@xxxxxxx>:
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@xxxxxxx>:
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
