From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Kamen Stanev <hambai@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: query io stats and finding a slow query
Date: Fri, 21 Sep 2007 11:35:15 -0500
Message-ID: <46F3AC73.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <de5063670709201436y5cbff0d0k70ade289a4c68199@mail.gmail.com>
References: <de5063670709201436y5cbff0d0k70ade289a4c68199@mail.gmail.com>
>>> On Thu, Sep 20, 2007 at 4:36 PM, in message
<de5063670709201436y5cbff0d0k70ade289a4c68199@mail.gmail.com>, "Kamen Stanev"
<hambai@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, hambai@gmail.com
Subject: Re: query io stats and finding a slow query
In-Reply-To: <46F3AC73.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox