Thread: Using EXPLAIN-ANALYZE
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
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
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
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.orgso that your message can get through to the mailing list cleanly
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 usedare generated during VACUUM ANALYZE/VACUUM FULL operations and, under 8.1, are probably maintained by autovacuum, butyou can always vacuum manually and see if that changes the query plan. -Owen
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
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