pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing EXPLAIN-ANALYZE
7+ messages / 4 participants
[nested] [flat]
* Using EXPLAIN-ANALYZE
@ 2006-02-16 20:07 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 2 replies; 7+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-02-16 20:07 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks a lot,
Kashmira Patel
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using EXPLAIN-ANALYZE
@ 2006-02-16 20:39 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Kashmira Patel (kupatel) <kupatel@cisco.com>
1 sibling, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2006-02-16 20:39 UTC (permalink / raw)
To: pgsql-sql
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_Explaining_Explain_Public.sxi;)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using EXPLAIN-ANALYZE
@ 2006-02-16 20:39 Abhishek <ajcool@gmail.com>
parent: Kashmira Patel (kupatel) <kupatel@cisco.com>
1 sibling, 0 replies; 7+ messages in thread
From: Abhishek @ 2006-02-16 20:39 UTC (permalink / raw)
To: Kashmira Patel (kupatel) <kupatel@cisco.com>; +Cc: pgsql-sql
http://www.postgresql.org/docs/7.2/static/performance-tips.html
EXPLAIN is explaied quite nicely with examples here. Hope that helps
Bests
AJ
On 2/16/06, Kashmira Patel (kupatel) <kupatel@cisco.com> 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?
>
> Thanks a lot,
> Kashmira Patel
>
--
Bests,
AJ
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using EXPLAIN-ANALYZE
@ 2006-02-16 21:08 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-02-16 21:08 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
So I would have to put in lots of rows of data in the table before using
the explain command?
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Thursday, February 16, 2006 12:39 PM
To: pgsql-sql@postgresql.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_Explaining
_Explain_Public.sxi>)
A
--
Andrew Sullivan | ajs@crankycanuck.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@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using EXPLAIN-ANALYZE
@ 2006-02-16 21:11 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 7+ messages in thread
From: Owen Jacobson @ 2006-02-16 21:11 UTC (permalink / raw)
To: pgsql-sql
Kashmira Patel wrote:
> So I would have to put in lots of rows of data in the table
> before using the explain command?
No, but PostgreSQL's query planner may take a different approach for a small table than a large one. The statistics used are generated during VACUUM ANALYZE/VACUUM FULL operations and, under 8.1, are probably maintained by autovacuum, but you can always vacuum manually and see if that changes the query plan.
-Owen
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using EXPLAIN-ANALYZE
@ 2006-02-16 21:14 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2006-02-16 21:14 UTC (permalink / raw)
To: Kashmira Patel (kupatel) <kupatel@cisco.com>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan
> Sent: Thursday, February 16, 2006 12:39 PM
> To: pgsql-sql@postgresql.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_Explaining
> _Explain_Public.sxi>)
>
> A
>
> --
> Andrew Sullivan | ajs@crankycanuck.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@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Using EXPLAIN-ANALYZE
@ 2006-02-16 21:15 Kashmira Patel (kupatel) <kupatel@cisco.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kashmira Patel \(kupatel\) @ 2006-02-16 21:15 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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@crankycanuck.ca]
Sent: Thursday, February 16, 2006 1:14 PM
To: Kashmira Patel (kupatel)
Cc: Andrew Sullivan; pgsql-sql@postgresql.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@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan
> Sent: Thursday, February 16, 2006 12:39 PM
> To: pgsql-sql@postgresql.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@crankycanuck.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@postgresql.org so that
your
> message can get through to the mailing list cleanly
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the
marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-02-16 21:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-16 20:07 Using EXPLAIN-ANALYZE Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-02-16 20:39 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-02-16 20:39 ` Abhishek <ajcool@gmail.com>
2006-02-16 21:08 Re: Using EXPLAIN-ANALYZE Kashmira Patel (kupatel) <kupatel@cisco.com>
2006-02-16 21:14 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-02-16 21:11 Re: Using EXPLAIN-ANALYZE Owen Jacobson <ojacobson@osl.com>
2006-02-16 21:15 Re: Using EXPLAIN-ANALYZE Kashmira Patel (kupatel) <kupatel@cisco.com>
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