Re: Interpreting query debug output

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Date: Tue, 18 May 2004 21:06:43 -0400
> To: Steve Lane <slane(at)moyergroup(dot)com>
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Interpreting query debug output
>
> Steve Lane <slane(at)moyergroup(dot)com> writes:
>> I have a database that is exhibiting sluggishness under load. Suspecting
>> that some queries may be poorly optimized, I turned on a fair amount of
>> debugging output in the logs. But I could use some help interpreting it.
>
> I think you're going at this all wrong. EXPLAIN ANALYZE should be the
> first tool you turn to, not low-level stats. Browsing the archives of
> the pgsql-performance mailing list may help you get started.
>
> regards, tom lane
>

Hmm. When I do a process listing I can see that there are postgres processes
occupying large chunks of CPU, sometimes in the 60-99% range, for long
enough to be noticeable in the process list. I'd like to capture those
process IDs and then correlate them with the stats captured in the log to
see why they take so much CPU.

If I want to EXPLAIN ANALYZE, I have to pick individual queries. The query
logic of the application is distributed across many source files. I'd have
to do quite some combing to recover a list of all queries the system runs. I
figure distilling the log output might be the best approach.

Given these points, is this still the wrong approach? Even if so, I'd still
love to know how to read the debug output.

-- sgl