Hi all,
I am (stilll) converting a database from a Clarion Topspeed database to Postgresql 7.4.5 on Debian Linux 2.6.6-1. The
programthat uses the database uses a query like "select * from table" to show the user the contents of a table. This
querycannot be changed (it is generated by Clarion and the person in charge of the program cannot alter that
behaviour).
Now I have a big performance problem with reading a large table ( 96713 rows). The query that is send to the database
is"select * from table".
"explain" and "explain analyze", using psql on cygwin:
munt=# explain select * from klt_alg;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on klt_alg (cost=0.00..10675.13 rows=96713 width=729)
munt=# explain analyze select * from klt_alg;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on klt_alg (cost=0.00..10675.13 rows=96713 width=729) (actual time=13.172..2553.328 rows=96713 loops=1)
Total runtime: 2889.109 ms
(2 rows)
Running the query (with pgAdmin III):
-- Executing query:
select * from klt_alg;
Total query runtime: 21926 ms.
Data retrieval runtime: 72841 ms.
96713 rows retrieved.
QUESTIONS:
GENERAL:
1. The manual says about "explain analyze" : "The ANALYZE option causes the statement to be actually executed, not only
planned.The total elapsed time expended within each plan node (in milliseconds) and total number of rows it actually
returnedare added to the display." Does this time include datatransfer or just the time the database needs to collect
thedata, without any data transfer?
2. If the time is without data transfer to the client, is there a reliable way to measure the time needed to run the
queryand get the data (without the overhead of a program that does something with the data)?
PGADMIN:
1. What does the "Total query runtime" really mean? (It was my understanding that it was the time the database needs to
collectthe data, without any data transfer).
2. What does the "Data retrieval runtime" really mean? (Is this including the filling of the datagrid/GUI, or just the
datatransfer?)
TIA
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
