Skip site navigation (1) Skip section navigation (2)

Measuring server performance with psql and pgAdmin

From: "Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)nl>
To: "Pgsql-Performance (E-mail)" <pgsql-performance(at)postgresql(dot)org>
Subject: Measuring server performance with psql and pgAdmin
Date: 2004-10-26 11:37:24
Message-ID: A3D1526C98B7C1409A687E0943EAC41001EACD@obelix.askesis.nl (view raw or flat)
Thread:
Lists: pgsql-performance
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: J(dot)Kraaijeveld(at)Askesis(dot)nl
web: www.askesis.nl 

Responses

pgsql-performance by date

Next:From: Simon RiggsDate: 2004-10-26 12:18:25
Subject: Re: [PATCHES] ARC Memory Usage analysis
Previous:From: Simon RiggsDate: 2004-10-26 09:50:58
Subject: Re: [PATCHES] ARC Memory Usage analysis

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group