Re: SQL query timing question

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "Dave Lazar" <hunkybill(at)gmail(dot)com>, <pgadmin-support(at)postgresql(dot)org>
Subject: Re: SQL query timing question
Date: 2005-06-03 07:37:52
Message-ID: E7F85A1B5FF8D44C8A1AF6885BC9A0E490E2B9@ratbert.vale-housing.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

> -----Original Message-----
> From: pgadmin-support-owner(at)postgresql(dot)org
> [mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of Dave Lazar
> Sent: 02 June 2005 20:00
> To: pgadmin-support(at)postgresql(dot)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.

Browse pgadmin-support by date

  From Date Subject
Next Message Andreas Pflug 2005-06-03 08:43:40 Re: SQL query timing question
Previous Message Dave Lazar 2005-06-02 19:00:00 SQL query timing question