Re: query io stats and finding a slow query

>>> On Thu, Sep 20, 2007 at 4:36 PM, in message
<de5063670709201436y5cbff0d0k70ade289a4c68199(at)mail(dot)gmail(dot)com>, "Kamen Stanev"
<hambai(at)gmail(dot)com> wrote:
>
> Is there a way to find which query is doing large io operations and/or which
> is using cached data and which is reading from disk.

A big part of your cache is normally in the OS, which makes that tough.

> please share your experience on how do you decide which
> queries to optimize and how to reorganize your database?

We base this on two things -- query metrics from our application framework
and user complaints about performance.

> Is there any tools that you use to profile your database.

Many people set log_min_duration_statement to get a look at long-running
queries.

When you identify a problem query, running it with EXPLAIN ANALYZE in front
will show you the plan with estimated versus actual counts, costs, and time.
This does actually execute the query (unlike EXPLAIN without ANALYZE).

-Kevin