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 program that uses the database uses a query like
"select * from table" to show the user the contents of a table. This query cannot 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 returned are added to
the display." Does this time include datatransfer or just the time the database needs
to collect the data, 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 query and 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 collect the 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: [EMAIL PROTECTED]
web: www.askesis.nl
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
