Julius Tuskenis <julius(at)nsoft(dot)lt> wrote:
> 2010.10.06 00:30, Kevin Grittner raš*:
>> There can be a number of reasons. The time to transmit the query
>> results to the client is one that jumps to mind, if the query
>> generates a large result set. Parts of what you described might
>> suggest checkpoint issues, depending on the query mix, the
>> PostgreSQL version, the hardware and the OS.
> Version string: PostgreSQL 8.3.4 on i686-pc-linux-gnu, compiled
> by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)
> https://sites.google.com/site/juliaustestukas/Home
Well, you're showing us a version string for a Linux PostgreSQL
server, but you link to a screen shot which appears to be a client
connecting to localhost on some variety of Windows. And you didn't
tell us much of anything else.
> I think the picture would explain the problem better then I can...
> I have put red ellipses around the times that are too different.
In my experience, when the time the server reports for query
execution is significantly less than the time the client reports for
running the query, the difference is time spent in the network or
client. Since you seem to be using localhost, I think we can rule
out the network as a significant factor.
> One more thing that may be connected to this
> We converted [the database] to UTF-8 from SQL_ASCII
I suppose it's not impossible that this could have some impact on
the client, particularly if it was competing with a busy database
server on the same machine.
> Please, could you provide information what actions does the
> postgres take after it does actions monitored by EXPLAIN ANALYZE?
I believe that by the time it comes up with that final timing, it
has completed the query run and put all results into the OS
networking buffers. What PostgreSQL would do next is wait for
another request.
-Kevin