Обсуждение: SQL query timing question
Hi, I have a table with 10 rows, and about 50 columns. Not much need for indexing. When I execute a select * command, it can take 18ms to run the query, but 1300ms for the data retrieval time for example... What controls this data retrieval timing? It seems like my query executes fast, but the bottleneck is this value?? Is there something in postgresql.conf that needs tweaking. I have a RH box with a 17Ghz P4 and 2 gigs RAM, and the bottleneck pgAdmin shows me appears to be this data retrieval time. Could someone more familiar with this please fill me in little. I thought it was RAM or HD speed slowing some PHP scripts down with SQL in them, but now, using just pgAdmin 1.2 to query the server directly I see the problem may be postgres setup. I realize that time may be the measure of the time it takes to transfer the data from the server to the pgAdmin client... but it also seems that the results are that slow being transferred from a request originating with a PHP script running on the server and connecting via ADOdb and localhost to the postmaster... what can be done to measure and or speed the data retrieval times??? Thanks!! Dave
> -----Original Message----- > From: pgadmin-support-owner@postgresql.org > [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Lazar > Sent: 02 June 2005 20:00 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] SQL query timing question > > Hi, > > I have a table with 10 rows, and about 50 columns. Not much need for > indexing. When I execute a select * command, it can take 18ms to run > the query, but 1300ms for the data retrieval time for example... > > What controls this data retrieval timing? It seems like my query > executes fast, but the bottleneck is this value?? Is there something > in postgresql.conf that needs tweaking. I have a RH box with a 17Ghz > P4 and 2 gigs RAM, and the bottleneck pgAdmin shows me appears to be > this data retrieval time. > > Could someone more familiar with this please fill me in little. I > thought it was RAM or HD speed slowing some PHP scripts down with SQL > in them, but now, using just pgAdmin 1.2 to query the server directly > I see the problem may be postgres setup. > > I realize that time may be the measure of the time it takes to > transfer the data from the server to the pgAdmin client... but it also > seems that the results are that slow being transferred from a request > originating with a PHP script running on the server and connecting via > ADOdb and localhost to the postmaster... what can be done to measure > and or speed the data retrieval times??? Hi, Have you run VACUUM FULL on the table recently? It sounds like it might get lots of updates which could be leaving hundreds or even thousands of dead tuples in the physical file - which consequently take a long time to scan to find the live ones. Regards, Dave.
Dave Lazar wrote: > Hi, > > I have a table with 10 rows, and about 50 columns. Not much need for > indexing. When I execute a select * command, it can take 18ms to run > the query, but 1300ms for the data retrieval time for example... > > What controls this data retrieval timing? It seems like my query > executes fast, but the bottleneck is this value?? Is there something > in postgresql.conf that needs tweaking. I have a RH box with a 17Ghz > P4 and 2 gigs RAM, and the bottleneck pgAdmin shows me appears to be > this data retrieval time. It's the GUI that takes up the time, i.e. the client, 18ms is postgresql backend and transport time, nothing to be tuned in postgresql.conf here. You're probably running pgAdmin on Linux, where the wxgtk implementation of wxListView is really slow with a lot of columns. Regards, Andeas