Thread: Report Generator Proposal
Hi all, each time that someone say: this query is slow, this query take forever, bla bla bla all our request are: 1) Which version are you running ? 2) May I see your table definition? 3) May I see you configuration file? 4) May I see the explain ? 5) ....... sometimes a beginner is scared about all these questions and even he/she don't know how do collect these informations and we lose important data about: "why that damn query is too slow"? may be is usefull generate an automatic report that take charge of collect all these informations and send it to a file or directly to pgsql-performance@postgresql.org , may be something like: > BEGIN REPORT TO FILE <file_name> > SELECT ...... > END REPORT; or > BEGIN REPORT TO EMAIL <address> > SELECT ...... > END REPORT; Regards Gaetano Mendola
Nobody entusiastic ? I'm wrong about my feeling ? I think that have the psql able to generate a report will increase the our better response to a user novice or not ? Regards Gaetano Mendola ----- Original Message ----- From: ""Gaetano Mendola"" <mendola@bigfoot.com> Newsgroups: comp.databases.postgresql.admin Sent: Wednesday, September 10, 2003 4:10 AM Subject: Report Generator Proposal > Hi all, > each time that someone say: this query is slow, this query take forever, bla > bla bla > all our request are: > > 1) Which version are you running ? > 2) May I see your table definition? > 3) May I see you configuration file? > 4) May I see the explain ? > 5) ....... > > sometimes a beginner is scared about all these questions and even > he/she don't know how do collect these informations and we lose > important data about: "why that damn query is too slow"? > > > may be is usefull generate an automatic report that take charge > of collect all these informations and send it to a file or directly to > pgsql-performance@postgresql.org , may be something like: > > > > BEGIN REPORT TO FILE <file_name> > > SELECT ...... > > END REPORT; > > > or > > > > BEGIN REPORT TO EMAIL <address> > > SELECT ...... > > END REPORT; > > > > Regards > Gaetano Mendola > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Must I suppose that the idea rejected ? ----- Original Message ----- From: ""Gaetano Mendola"" <mendola@bigfoot.com> Newsgroups: comp.databases.postgresql.admin Sent: Thursday, September 11, 2003 11:52 PM Subject: Re: Report Generator Proposal > Nobody entusiastic ? I'm wrong about my feeling ? > I think that have the psql able to generate a report > will increase the our better response to a user novice > or not ? > > > Regards > Gaetano Mendola > > ----- Original Message ----- > From: ""Gaetano Mendola"" <mendola@bigfoot.com> > Newsgroups: comp.databases.postgresql.admin > Sent: Wednesday, September 10, 2003 4:10 AM > Subject: Report Generator Proposal > > > > Hi all, > > each time that someone say: this query is slow, this query take forever, > bla > > bla bla > > all our request are: > > > > 1) Which version are you running ? > > 2) May I see your table definition? > > 3) May I see you configuration file? > > 4) May I see the explain ? > > 5) ....... > > > > sometimes a beginner is scared about all these questions and even > > he/she don't know how do collect these informations and we lose > > important data about: "why that damn query is too slow"? > > > > > > may be is usefull generate an automatic report that take charge > > of collect all these informations and send it to a file or directly to > > pgsql-performance@postgresql.org , may be something like: > > > > > > > BEGIN REPORT TO FILE <file_name> > > > SELECT ...... > > > END REPORT; > > > > > > or > > > > > > > BEGIN REPORT TO EMAIL <address> > > > SELECT ...... > > > END REPORT; > > > > > > > > Regards > > Gaetano Mendola > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
I rather like it actually. Cisco equipment has a 'show tech-support' command that does exactly that, dumps all the config,HW/SW versions, current state, you name it. If you have a problem you run that, attach the output to yr support email, and 99%of the time there's enough info there to solve the problem. It'd be easy to automate all except the explain, a script that did everything else could then emit "If your preoblem relatesto a specific query, then run 'explain analyze <query>' and attach it to your message as well". > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola > Sent: 15 September 2003 16:25 > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Report Generator Proposal > > > Must I suppose that the idea rejected ? > > ----- Original Message ----- > From: ""Gaetano Mendola"" <mendola@bigfoot.com> > Newsgroups: comp.databases.postgresql.admin > Sent: Thursday, September 11, 2003 11:52 PM > Subject: Re: Report Generator Proposal > > > > Nobody entusiastic ? I'm wrong about my feeling ? > > I think that have the psql able to generate a report > > will increase the our better response to a user novice > > or not ? > > > > > > Regards > > Gaetano Mendola > > > > ----- Original Message ----- > > From: ""Gaetano Mendola"" <mendola@bigfoot.com> > > Newsgroups: comp.databases.postgresql.admin > > Sent: Wednesday, September 10, 2003 4:10 AM > > Subject: Report Generator Proposal > > > > > > > Hi all, > > > each time that someone say: this query is slow, this query take forever, > > bla > > > bla bla > > > all our request are: > > > > > > 1) Which version are you running ? > > > 2) May I see your table definition? > > > 3) May I see you configuration file? > > > 4) May I see the explain ? > > > 5) ....... > > > > > > sometimes a beginner is scared about all these questions and even > > > he/she don't know how do collect these informations and we lose > > > important data about: "why that damn query is too slow"? > > > > > > > > > may be is usefull generate an automatic report that take charge > > > of collect all these informations and send it to a file or directly to > > > pgsql-performance@postgresql.org , may be something like: > > > > > > > > > > BEGIN REPORT TO FILE <file_name> > > > > SELECT ...... > > > > END REPORT; > > > > > > > > > or > > > > > > > > > > BEGIN REPORT TO EMAIL <address> > > > > SELECT ...... > > > > END REPORT; > > > > > > > > > > > > Regards > > > Gaetano Mendola > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
""Matt Clark"" <matt@ymogen.net> wrote: > It'd be easy to automate all except the explain, a script that did everything else could then emit "If your preoblem relates to a > specific query, then run 'explain analyze <query>' and attach it to your message as well". Well, must be charge of psql store somewhere all selects performed between the BEGIN REPORT and END REPORT and perform the explain command of that selects with the explain; may be the really difficult here is that psql shall know all tables involved in a select and insert in the report the descriptions of that tables. Before to work on it I want be sure that is a good idea. Regards Gaetano Mendola > > -----Original Message----- > > From: pgsql-admin-owner@postgresql.org > > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola > > Sent: 15 September 2003 16:25 > > To: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] Report Generator Proposal > > > > > > Must I suppose that the idea rejected ? > > > > ----- Original Message ----- > > From: ""Gaetano Mendola"" <mendola@bigfoot.com> > > Newsgroups: comp.databases.postgresql.admin > > Sent: Thursday, September 11, 2003 11:52 PM > > Subject: Re: Report Generator Proposal > > > > > > > Nobody entusiastic ? I'm wrong about my feeling ? > > > I think that have the psql able to generate a report > > > will increase the our better response to a user novice > > > or not ? > > > > > > > > > Regards > > > Gaetano Mendola > > > > > > ----- Original Message ----- > > > From: ""Gaetano Mendola"" <mendola@bigfoot.com> > > > Newsgroups: comp.databases.postgresql.admin > > > Sent: Wednesday, September 10, 2003 4:10 AM > > > Subject: Report Generator Proposal > > > > > > > > > > Hi all, > > > > each time that someone say: this query is slow, this query take forever, > > > bla > > > > bla bla > > > > all our request are: > > > > > > > > 1) Which version are you running ? > > > > 2) May I see your table definition? > > > > 3) May I see you configuration file? > > > > 4) May I see the explain ? > > > > 5) ....... > > > > > > > > sometimes a beginner is scared about all these questions and even > > > > he/she don't know how do collect these informations and we lose > > > > important data about: "why that damn query is too slow"? > > > > > > > > > > > > may be is usefull generate an automatic report that take charge > > > > of collect all these informations and send it to a file or directly to > > > > pgsql-performance@postgresql.org , may be something like: > > > > > > > > > > > > > BEGIN REPORT TO FILE <file_name> > > > > > SELECT ...... > > > > > END REPORT; > > > > > > > > > > > > or > > > > > > > > > > > > > BEGIN REPORT TO EMAIL <address> > > > > > SELECT ...... > > > > > END REPORT; > > > > > > > > > > > > > > > > Regards > > > > Gaetano Mendola > > > > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 8: explain analyze is your friend > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
On Monday, September 15, 2003, at 02:06 PM, Gaetano Mendola wrote: > ""Matt Clark"" <matt@ymogen.net> wrote: >> It'd be easy to automate all except the explain, a script that did > everything else could then emit "If your preoblem relates to a >> specific query, then run 'explain analyze <query>' and attach it to >> your > message as well". > > Well, must be charge of psql store somewhere all selects performed > between the BEGIN REPORT and END REPORT and perform > the explain command of that selects with the explain; may be the really > difficult here is that psql > shall know all tables involved in a select and insert in the report the > descriptions of that tables. > Before to work on it I want be sure that is a good idea. > > Regards > Gaetano Mendola sounds good. maybe with the option (enabled by default?) to only show the columns used in the queries for the tables pulled. ((wheat from chaff and all that)) hmm. though there's still the (some small percentage of) things that could be going wrong with custom stored procedures. dumping those would be (I'm presuming, based on the scope of the task as it's been described) reasonably simply, but figuring out what they touch could be prohibitive? kudos for any attempt, though, all around. :) -kaolin fire -postgresql novice -http://poemranker.com
Sounds good ...it will save lot of emails floating around and time..
Regards,
Somasekhar Bangalore
Principal Software Engineer
ZenSutra Software Technologies Pvt. Ltd.
Suite 601, HM Geneva House
#14, Cunningham Road
Bangalore 560-052, India
Ph:+91-80-235-0481
Fax:+91-80-235-0486
Email: sbangalore@zensutra.com
Weaving the knowledge tapestry'
-----Original Message-----
From: Matt Clark [mailto:matt@ymogen.net]
Sent: Monday, September 15, 2003 11:00 PM
To: Gaetano Mendola; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Report Generator Proposal
I rather like it actually. Cisco equipment has a 'show tech-support' command that does exactly that, dumps all the
config,HW/SW
versions, current state, you name it. If you have a problem you run that, attach the output to yr support email, and
99%of the
time there's enough info there to solve the problem.
It'd be easy to automate all except the explain, a script that did everything else could then emit "If your preoblem
relatesto a
specific query, then run 'explain analyze <query>' and attach it to your message as well".
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola
> Sent: 15 September 2003 16:25
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Report Generator Proposal
>
>
> Must I suppose that the idea rejected ?
>
> ----- Original Message -----
> From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> Newsgroups: comp.databases.postgresql.admin
> Sent: Thursday, September 11, 2003 11:52 PM
> Subject: Re: Report Generator Proposal
>
>
> > Nobody entusiastic ? I'm wrong about my feeling ?
> > I think that have the psql able to generate a report
> > will increase the our better response to a user novice
> > or not ?
> >
> >
> > Regards
> > Gaetano Mendola
> >
> > ----- Original Message -----
> > From: ""Gaetano Mendola"" <mendola@bigfoot.com>
> > Newsgroups: comp.databases.postgresql.admin
> > Sent: Wednesday, September 10, 2003 4:10 AM
> > Subject: Report Generator Proposal
> >
> >
> > > Hi all,
> > > each time that someone say: this query is slow, this query take forever,
> > bla
> > > bla bla
> > > all our request are:
> > >
> > > 1) Which version are you running ?
> > > 2) May I see your table definition?
> > > 3) May I see you configuration file?
> > > 4) May I see the explain ?
> > > 5) .......
> > >
> > > sometimes a beginner is scared about all these questions and even
> > > he/she don't know how do collect these informations and we lose
> > > important data about: "why that damn query is too slow"?
> > >
> > >
> > > may be is usefull generate an automatic report that take charge
> > > of collect all these informations and send it to a file or directly to
> > > pgsql-performance@postgresql.org , may be something like:
> > >
> > >
> > > > BEGIN REPORT TO FILE <file_name>
> > > > SELECT ......
> > > > END REPORT;
> > >
> > >
> > > or
> > >
> > >
> > > > BEGIN REPORT TO EMAIL <address>
> > > > SELECT ......
> > > > END REPORT;
> > >
> > >
> > >
> > > Regards
> > > Gaetano Mendola
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 8: explain analyze is your friend
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Oracle have something similar: RDA which generates HTML reports for the hardware, SO, and database.
It doesn't do explains but does show which SQLs are the most expensive, etc.
It could be worth having a look at that for ideas on what and how to report it.
Stephen.
Somasekhar Bangalore wrote:
It doesn't do explains but does show which SQLs are the most expensive, etc.
It could be worth having a look at that for ideas on what and how to report it.
Stephen.
Somasekhar Bangalore wrote:
Sounds good ...it will save lot of emails floating around and time.. Regards, Somasekhar Bangalore Principal Software Engineer ZenSutra Software Technologies Pvt. Ltd. Suite 601, HM Geneva House #14, Cunningham Road Bangalore 560-052, India Ph:+91-80-235-0481 Fax:+91-80-235-0486 Email: sbangalore@zensutra.com Weaving the knowledge tapestry' -----Original Message----- From: Matt Clark [mailto:matt@ymogen.net] Sent: Monday, September 15, 2003 11:00 PM To: Gaetano Mendola; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Report Generator Proposal I rather like it actually. Cisco equipment has a 'show tech-support' command that does exactly that, dumps all the config, HW/SW versions, current state, you name it. If you have a problem you run that, attach the output to yr support email, and 99% of the time there's enough info there to solve the problem. It'd be easy to automate all except the explain, a script that did everything else could then emit "If your preoblem relates to a specific query, then run 'explain analyze <query>' and attach it to your message as well".-----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gaetano Mendola Sent: 15 September 2003 16:25 To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Report Generator Proposal Must I suppose that the idea rejected ? ----- Original Message ----- From: ""Gaetano Mendola"" <mendola@bigfoot.com> Newsgroups: comp.databases.postgresql.admin Sent: Thursday, September 11, 2003 11:52 PM Subject: Re: Report Generator ProposalNobody entusiastic ? I'm wrong about my feeling ? I think that have the psql able to generate a report will increase the our better response to a user novice or not ? Regards Gaetano Mendola ----- Original Message ----- From: ""Gaetano Mendola"" <mendola@bigfoot.com> Newsgroups: comp.databases.postgresql.admin Sent: Wednesday, September 10, 2003 4:10 AM Subject: Report Generator ProposalHi all, each time that someone say: this query is slow, this query take forever,blabla bla all our request are: 1) Which version are you running ? 2) May I see your table definition? 3) May I see you configuration file? 4) May I see the explain ? 5) ....... sometimes a beginner is scared about all these questions and even he/she don't know how do collect these informations and we lose important data about: "why that damn query is too slow"? may be is usefull generate an automatic report that take charge of collect all these informations and send it to a file or directly to pgsql-performance@postgresql.org , may be something like:BEGIN REPORT TO FILE <file_name> SELECT ...... END REPORT;orBEGIN REPORT TO EMAIL <address> SELECT ...... END REPORT;Regards Gaetano Mendola ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Report Generator in PHP-GTK that work with Pear-db. It's very easy for users... http://agata.codigolivre.org.br Creator: Pablo (pablo@php.net) []s Mauricio On Monday 15 September 2003 18:25, kaolin fire wrote: > On Monday, September 15, 2003, at 02:06 PM, Gaetano Mendola wrote: > > ""Matt Clark"" <matt@ymogen.net> wrote: > >> It'd be easy to automate all except the explain, a script that did > > > > everything else could then emit "If your preoblem relates to a > > > >> specific query, then run 'explain analyze <query>' and attach it to > >> your > > > > message as well". > > > > Well, must be charge of psql store somewhere all selects performed > > between the BEGIN REPORT and END REPORT and perform > > the explain command of that selects with the explain; may be the really > > difficult here is that psql > > shall know all tables involved in a select and insert in the report the > > descriptions of that tables. > > Before to work on it I want be sure that is a good idea. > > > > Regards > > Gaetano Mendola > > sounds good. maybe with the option (enabled by default?) to only show > the columns used in the queries for the tables pulled. ((wheat from > chaff and all that)) > > hmm. though there's still the (some small percentage of) things that > could be going wrong with custom stored procedures. dumping those > would be (I'm presuming, based on the scope of the task as it's been > described) reasonably simply, but figuring out what they touch could be > prohibitive? > > kudos for any attempt, though, all around. :) > > -kaolin fire > -postgresql novice > -http://poemranker.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Maurício de Castro Coordenador de Desenvolvimento em Software Livre SOLIS - Cooperativa de Soluções Livres http://www.solis.coop.br 51 3011 0610
"Mauricio de Castro" <mcastro@solis.coop.br> wrote:
> Report Generator in PHP-GTK that work with Pear-db. It's very easy for
> users...
I was thinking to generate the report with the psql, not with an external
program, somethink like this:
Welcome to psql 7.X, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=# BEGIN REPORT TO my_report_file;
BEGIN REPORT;
test=# select * from ........
[SNIPPED RESULT]
test=# END REPORT;
END REPORT;
Regards
Gaetano Mendola