Subject: Re: how to use explain analyze
Mime-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
From: Brent Dombrowski <brent.dombrowski@gmail.com>
In-Reply-To: 
 <7be7d91b-c054-41c6-8a7c-08e77d29625e@v2g2000yqf.googlegroups.com>
Date: Thu, 27 Oct 2011 10:06:49 -0700
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <88D946EE-F9E8-4DC0-8140-0C0C3F378328@gmail.com>
References: <7be7d91b-c054-41c6-8a7c-08e77d29625e@v2g2000yqf.googlegroups.com>
To: alan <alan.miller3@gmail.com>

On Oct 25, 2011, at 7:12 AM, alan wrote:

> I'm new to postgres and was wondering how to use EXPLAIN ANALYZE ....
>=20
> Can I use the output from ANALYZE EXPLAIN to estimate or predict the
> actual time
> it would take for a given query to return?
>=20
> 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.
>=20
> 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.
>  }
>=20
> 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.