From: Brent Dombrowski <brent.dombrowski@gmail.com>
To: alan <alan.miller3@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to use explain analyze
Date: Thu, 27 Oct 2011 10:06:49 -0700
Message-ID: <88D946EE-F9E8-4DC0-8140-0C0C3F378328@gmail.com> (raw)
In-Reply-To: <7be7d91b-c054-41c6-8a7c-08e77d29625e@v2g2000yqf.googlegroups.com>
References: <7be7d91b-c054-41c6-8a7c-08e77d29625e@v2g2000yqf.googlegroups.com>
On Oct 25, 2011, at 7:12 AM, alan wrote:
> I'm new to postgres and was wondering how to use EXPLAIN ANALYZE ....
>
> Can I use the output from ANALYZE EXPLAIN to estimate or predict the
> actual time
> it would take for a given query to return?
>
> I ask because I'm writing a typical web app that allows the user to
> build and submit a query
> to my DB. Since I don't know how "simple" or "complex" the user-
> generated queries will be
> I thought it might be possible to use the ANALYZE EXPLAIN output to
> make a "guestimation"
> about the expected return time of the query.
>
> I'd like to use this in my web-app to determine whether to run the
> query in real-time (user waits
> for results) or queue up the query (and notify the user once the query
> is finished). E.g.:
> if (the Total runtime" reported by explain analyze is > n ms) {
> tell the user that his request was submitted for processing, and
> notify the user once resuilts are available
> } else {
> run the query and wait for the results in real time.
> }
>
> Thanks,
> Alan
Check out the docs on EXPLAIN, http://www.postgresql.org/docs/9.0/static/sql-explain.html
EXPLAIN will give you an estimation. How well the internal statistics match the actual data will determine how close the estimation is.
EXPLAIN ANALYZE will give you an estimation and the actuals. WARNING: EXPLAIN ANALYZE carries out the query. If there any data modifications in the query (UPDATE, INSERT, etc.) they will be carried out.
Note: ANALYZE is a different command and EXPLAIN is not a valid option for it. You'll be researching this command if the estimates are way off.
Brent.
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-sql@postgresql.org
Cc: brent.dombrowski@gmail.com, alan.miller3@gmail.com
Subject: Re: how to use explain analyze
In-Reply-To: <88D946EE-F9E8-4DC0-8140-0C0C3F378328@gmail.com>
* 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