Re: Using EXPLAIN-ANALYZE

Thanks for the explanations and the links. Will try some of this out and
come back to you guys if I still don't understand :)

-Kashmira

-----Original Message-----
From: Andrew Sullivan [mailto:ajs(at)crankycanuck(dot)ca]
Sent: Thursday, February 16, 2006 1:14 PM
To: Kashmira Patel (kupatel)
Cc: Andrew Sullivan; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Using EXPLAIN-ANALYZE

On Thu, Feb 16, 2006 at 01:08:40PM -0800, Kashmira Patel (kupatel)
wrote:
> So I would have to put in lots of rows of data in the table before
> using the explain command?

Well, no, but you won't get useful information without it.
PostgreSQL has a cost-based optimizer. The query plan is affected by
the nature of your data. That's what the ANALYZE command (on its own,
or with VACUUM) is for.

A

>
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: Thursday, February 16, 2006 12:39 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] Using EXPLAIN-ANALYZE
>
> On Thu, Feb 16, 2006 at 12:07:10PM -0800, Kashmira Patel (kupatel)
> wrote:
> > Hi all,
> > I am pretty new to using Postrgres, and have been looking at the
> > messages in this forum for a while. I have noticed that the use of
> > EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres
> > docs, but am not quite sure I understand how this works. Is there
> > some tutorial or any other documentation how this can be used?
>
> Well, here's the short version:
>
> EXPLAIN [query] tells you what the planner _thinks_ it should do.
>
> EXPLAIN ANALYZE [query] tells you what the planner thinks it should
> do, and also executes the query and reports back how long every step
> took, how many rows were returned, &c. (For this reason, you want to
> wrap it in BEGIN;...;ROLLBACK; if it changes data.)
>
> For more, see the EXPLAIN EXPLAINED tutorial on
> techdocs.postgresql.org
> (<http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaini
> ng
> _Explain_Public.sxi>)
>
> A
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca In the future this spectacle of

> the middle classes shocking the avant- garde will probably become the
> textbook definition of Postmodernism.
> --Brad Holland
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
your
> message can get through to the mailing list cleanly

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the
marketplace.
--Philip Greenspun