On 26/07/10 16:35, Piotr Gasidło wrote: > Hello, > > I've found strange problem in my database (8.4.4, but also 9.0beta3, > default postgresql.conf, shared_buffers raised to 256MB). > > EXPLAIN ANALYZE SELECT ... > Total runtime: 4.782 ms > Time: 25,970 ms > > SELECT ... > ... > (21 rows) > > Time: 23,042 ms > > Test done in psql connected by socket to server (same host, using > \timing to get runtime). > > Does big difference in "Total runtime" and "Time" is normal? Given that EXPLAIN ANALYZE doesn't transfer large rowsets to the client, it can't really be time taken to transfer the data, which is the usual difference between 'explain analyze' timings and psql client-side timings. Given that, I'm wondering if the difference in this case is planning time. I can't really imagine the query planner taking 20 seconds (!!) to run, though, no matter how horrifyingly complicated the query and table structure were, unless there was something going wrong. Another possibility, then, is that for some reason queries are being delayed from starting or delayed before results are being returned, so the server completes them in a short amount of time but it takes a while for psql to find out they're finished. In your position, at this point I'd be doing things like hooking a debugger up to the postgres backend and interrupting its execution periodically to see what it's up to while this query runs. I'd also be using wireshark to look at network activity to see if there were any clues there. I'd be using "top", "vmstat" and "iostat" to examine system-level load if it was practical to leave the system otherwise idle, so I could see if CPU/memory/disk were in demand, and for how long. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
