Обсуждение: explain
Hello all,
Can someone point me how can i make explain plan for a query?
--
Popeanga Marian
DBA Oracle
CNLO Romania
Popeanga Marian wrote: > Hello all, > > > Can someone point me how can i make explain plan for a query? I don't want this for pgsql, but from any other tool that connects to postgres. -- Popeanga Marian DBA Oracle CNLO Romania
On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote: > Hello all, > > > Can someone point me how can i make explain plan for a query? explain select blah from foo where bar = baz; \h explain Look at the PostgreSQL documentation (SQL Reference). -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + "There's 2 kind of people: those who knows assembler and the rest"
<br /><br /> Roberto Mello wrote:<br /><blockquote cite="mid20030321063435.GA6863@cc.usu.edu" type="cite"><pre
wrap="">OnFri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote: </pre><blockquote type="cite"><pre
wrap="">Helloall,
Can someone point me how can i make explain plan for a query? </pre></blockquote><pre wrap="">
explain select blah from foo where bar = baz;
\h explain
Look at the PostgreSQL documentation (SQL Reference).
-Roberto
</pre></blockquote> I have look at this command. I want to know how can i make this from other tool than pgsql.<br
/>In oracle when i issue an explain plan the explain insert values into a table ( plan_table ).<br /> Then i could
selectthe values from plan_table. Is there similar in postgres ?<br /><br /><pre class="moz-signature"
cols="$mailwrapcol">--
Popeanga Marian
DBA Oracle
CNLO Romania
</pre><br />
Dear Marian, in postgresql 7.3 explain commands retruns as set of rows. The result of EXPLAIN can just be fecthed as result of any other SELECT query and manipulated further. i hope it helps. sorry if i did not get ur problem rite. regds mallah. On Friday 21 Mar 2003 11:51 am, Popeanga Marian wrote: > > > Roberto Mello wrote: > > >On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote: > > > > > >>Hello all, > >> > >> > >> Can someone point me how can i make explain plan for a query? > >> > >> > > > >explain select blah from foo where bar = baz; > > > >\h explain > > > >Look at the PostgreSQL documentation (SQL Reference). > > > >-Roberto > > > > > > > I have look at this command. I want to know how can i make this from > other tool than pgsql. > In oracle when i issue an explain plan the explain insert values into a > table ( plan_table ). > Then i could select the values from plan_table. Is there similar > in postgres ? > > -- > Popeanga Marian > DBA Oracle > CNLO Romania > > > -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Thanks for your repply.<br /><br /> This is what i expected too. Can you point me from where i can get this
result?<br /><br /> explain select * from pg_tables where tablename=:tab;<br /><br /><br /> select * from ... ?<br
/><br/><br /><br /> In Oracle i will do:<br /> explain plan set statement_id=uniq_id_for_select for <br />
select * from dba_tables where table_name=:tab<br /><br /> SELECT * FROM plan_table WHERE Statement_ID =
this_uniq_id_for_select;<br/><br /><br /><br /><br /> Rajesh Kumar Mallah wrote:<br /><blockquote
cite="mid200303211353.17208.mallah@trade-india.com"type="cite"><pre wrap="">
Dear Marian,
in postgresql 7.3 explain commands retruns as
set of rows.
The result of EXPLAIN can just be fecthed as result
of any other SELECT query and manipulated further.
i hope it helps.
sorry if i did not get ur problem rite.
regds
mallah.
On Friday 21 Mar 2003 11:51 am, Popeanga Marian wrote: </pre><blockquote type="cite"><pre wrap="">
Roberto Mello wrote:
</pre><blockquote type="cite"><pre wrap="">On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote:
</pre><blockquote type="cite"><pre wrap="">Hello all,
Can someone point me how can i make explain plan for a query?
</pre></blockquote><pre wrap="">explain select blah from foo where bar = baz;
\h explain
Look at the PostgreSQL documentation (SQL Reference).
-Roberto
</pre></blockquote><pre wrap=""> I have look at this command. I want to know how can i make this from
other tool than pgsql.
In oracle when i issue an explain plan the explain insert values into a
table ( plan_table ).
Then i could select the values from plan_table. Is there similar
in postgres ?
--
Popeanga Marian
DBA Oracle
CNLO Romania
</pre></blockquote><pre wrap=""> </pre></blockquote><br /><pre class="moz-signature" cols="$mailwrapcol">--
Popeanga Marian
DBA Oracle
CNLO Romania
</pre><br />
<br /><br /> Popeanga Marian wrote:<br /><blockquote cite="mid3E7ACD00.6050702@cnlo.ro" type="cite"></blockquote>
Thanksfor your repply.<br /><br /> This is what i expected too. Can you point me from where i can get this
result?<br /><br /> explain select * from pg_tables where tablename=:tab;<br /><br /><br /> select * from ... ?<br
/><br/><br /><br /> In Oracle i will do:<br /> explain plan set statement_id=uniq_id_for_select for <br />
select * from dba_tables where table_name=:tab<br /><br /> SELECT * FROM plan_table WHERE Statement_ID =
this_uniq_id_for_select;<br/><br /><br /><br /> I need this at programming level, not from within pgsql tool.<br /> I
tryeddirectly with explain select ... but it doesn't retrive any tuples!<br /><br /><blockquote
cite="mid3E7ACD00.6050702@cnlo.ro"type="cite"><br /> Rajesh Kumar Mallah wrote:<br /><blockquote
cite="mid200303211353.17208.mallah@trade-india.com"type="cite"><pre wrap="">
Dear Marian,
in postgresql 7.3 explain commands retruns as
set of rows.
The result of EXPLAIN can just be fecthed as result
of any other SELECT query and manipulated further.
i hope it helps.
sorry if i did not get ur problem rite.
regds
mallah.
On Friday 21 Mar 2003 11:51 am, Popeanga Marian wrote: </pre><blockquote type="cite"><pre wrap="">Roberto Mello wrote:
</pre><blockquote type="cite"><pre wrap="">On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote:
</pre><blockquote type="cite"><pre wrap="">Hello all,
Can someone point me how can i make explain plan for a query?
</pre></blockquote><pre wrap="">explain select blah from foo where bar = baz;
\h explain
Look at the PostgreSQL documentation (SQL Reference).
-Roberto
</pre></blockquote><pre wrap=""> I have look at this command. I want to know how can i make this from
other tool than pgsql.
In oracle when i issue an explain plan the explain insert values into a
table ( plan_table ).
Then i could select the values from plan_table. Is there similar
in postgres ?
--
Popeanga Marian
DBA Oracle
CNLO Romania
</pre></blockquote><pre wrap=""> </pre></blockquote><br /><pre class="moz-signature" cols="$mailwrapcol">--
Popeanga Marian
DBA Oracle
CNLO Romania </pre><br /></blockquote><br /><pre class="moz-signature" cols="$mailwrapcol">--
Popeanga Marian
DBA Oracle
CNLO Romania
</pre><br />
Hi, Marian i am not too familiar with oracle features. Can you tell us the problem you are trying to solve. lemme try to understand the below. > > In Oracle i will do: > > explain plan set statement_id=uniq_id_for_select for > > select * from dba_tables where table_name=:tab what does it do ? assigning a uniq_id to the query "select * from dba_tables where table_name=:tab" is the query an example query or a fixed method of accomplising something? > > > > SELECT * FROM plan_table WHERE Statement_ID = this_uniq_id_for_select; > > is plan_table supposed to store execution plans of all the queries being executed by dbserver , or only certain queries you are marking by some mechanism? In case you are wanting to see the execution plan and query times of each and every query there is a crude method. (DISCLAIMER: the opinions are mine and does not reflects others') enable logging to appropriate levels , postmaster can log execution plans and timings in a file. you will find more details in Admin Docs. Regds mallah. On Friday 21 Mar 2003 4:30 pm, Popeanga Marian wrote: > > > Popeanga Marian wrote: > > > Thanks for your repply. > > > > This is what i expected too. Can you point me from where i > > can get this result ? > > > > explain select * from pg_tables where tablename=:tab; > > > > > > select * from ... ? > > > > > > > > > > > I need this at programming level, not from within pgsql tool. > I tryed directly with explain select ... but it doesn't retrive any tuples! > > > > > Rajesh Kumar Mallah wrote: > > > >>Dear Marian, > >> > >>in postgresql 7.3 explain commands retruns as > >>set of rows. > >> > >>The result of EXPLAIN can just be fecthed as result > >>of any other SELECT query and manipulated further. > >> > >>i hope it helps. > >> > >>sorry if i did not get ur problem rite. > >> > >> > >>regds > >>mallah. > >> > >> > >> > >> > >>On Friday 21 Mar 2003 11:51 am, Popeanga Marian wrote: > >> > >> > >>>Roberto Mello wrote: > >>> > >>> > >>> > >>>>On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote: > >>>> > >>>> > >>>> > >>>> > >>>>>Hello all, > >>>>> > >>>>> > >>>>> Can someone point me how can i make explain plan for a query? > >>>>> > >>>>> > >>>>> > >>>>> > >>>>explain select blah from foo where bar = baz; > >>>> > >>>>\h explain > >>>> > >>>>Look at the PostgreSQL documentation (SQL Reference). > >>>> > >>>>-Roberto > >>>> > >>>> > >>>> > >>>> > >>>> > >>> I have look at this command. I want to know how can i make this from > >>>other tool than pgsql. > >>>In oracle when i issue an explain plan the explain insert values into a > >>>table ( plan_table ). > >>>Then i could select the values from plan_table. Is there similar > >>>in postgres ? > >>> > >>>-- > >>>Popeanga Marian > >>>DBA Oracle > >>>CNLO Romania > >>> > >>> > >>> > >>> > >>> > >> > >> > >> > > > >-- > >Popeanga Marian > >DBA Oracle > >CNLO Romania > > > > > > > > -- > Popeanga Marian > DBA Oracle > CNLO Romania > > > -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Ok i'll try to explain you in detail what i am trying to accomplish.<br /><br /> I am working at a cross platform tool foradministration (and many more ) of Oracle , Postgresql, MySql<br /><a class="moz-txt-link-freetext" href="http://cptd-proj.sourceforge.net">http://cptd-proj.sourceforge.net</a><br/><br /> I don't have many knowledgeas a postgresql user or dba. I have worked with Oracle mainly.<br /><br /><br /> In Oracle explain plans are storedinto a special table named PLAN_TABLE so when i issue<br /><pre wrap="">explain plan set statement_id=uniq_id_for_selectfor select * from dba_tables where table_name=:tab </pre> <pre wrap=""></pre><br /> new entrys with the explain plan are inserted into this special table.<br /> And you canselect those entrys from this PLAN_TABLE and show it into a widget. <br /><br /><pre wrap="">SELECT * FROM plan_tableWHERE Statement_ID = uniq_id_for_select</pre><br /> Now the issue i encounter is that i want to show the excutionplan for connections against PostgreSql DB too, but i don't know from where to find this information.<br /> I noticedthat pgsql is able to explain plan for selects.<br /><br /> But how ?<br /><br /><br /> Rajesh Kumar Mallah wrote:<br/><blockquote cite="mid200303211740.27016.mallah@trade-india.com" type="cite"><pre wrap=""> Hi, Marian i am not too familiar with oracle features. Can you tell us the problem you are trying to solve. lemme try to understand the below. </pre><blockquote type="cite"><blockquote type="cite"><pre wrap="">In Oracle i will do: explain plan set statement_id=uniq_id_for_select for select * from dba_tables where table_name=:tab </pre></blockquote></blockquote><prewrap=""> what does it do ? assigning a uniq_id to the query "select * from dba_tables where table_name=:tab" is the query an example query or a fixed method of accomplising something? </pre><blockquote type="cite"><blockquote type="cite"><pre wrap="">SELECT * FROM plan_table WHERE Statement_ID = this_uniq_id_for_select; </pre></blockquote></blockquote><pre wrap=""> is plan_table supposed to store execution plans of all the queries being executed by dbserver , or only certain queries you are marking by some mechanism? In case you are wanting to see the execution plan and query times of each and every query there is a crude method. (DISCLAIMER: the opinions are mine and does not reflects others') enable logging to appropriate levels , postmaster can log execution plans and timings in a file. you will find more details in Admin Docs. Regds mallah. On Friday 21 Mar 2003 4:30 pm, Popeanga Marian wrote: </pre><blockquote type="cite"><pre wrap=""> Popeanga Marian wrote: </pre><blockquote type="cite"><pre wrap="">Thanks for your repply. This is what i expected too. Can you point me from where i can get this result ? explain select * from pg_tables where tablename=:tab; select * from ... ? </pre></blockquote><pre wrap="">I need this at programming level, not from within pgsql tool. I tryed directly with explain select ... but it doesn't retrive any tuples! </pre><blockquote type="cite"><pre wrap="">Rajesh Kumar Mallah wrote: </pre><blockquote type="cite"><pre wrap="">Dear Marian, in postgresql 7.3 explain commands retruns as set of rows. The result of EXPLAIN can just be fecthed as result of any other SELECT query and manipulated further. i hope it helps. sorry if i did not get ur problem rite. regds mallah. On Friday 21 Mar 2003 11:51 am, Popeanga Marian wrote: </pre><blockquote type="cite"><pre wrap="">Roberto Mello wrote: </pre><blockquote type="cite"><pre wrap="">On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote: </pre><blockquote type="cite"><pre wrap="">Hello all, Can someone point me how can i make explain plan for a query? </pre></blockquote><pre wrap="">explain select blah from foo where bar = baz; \h explain Look at the PostgreSQL documentation (SQL Reference). -Roberto </pre></blockquote><pre wrap=""> I have look at this command. I want to know how can i make this from other tool than pgsql. In oracle when i issue an explain plan the explain insert values into a table ( plan_table ). Then i could select the values from plan_table. Is there similar in postgres ? -- Popeanga Marian DBA Oracle CNLO Romania </pre></blockquote><pre wrap=""> </pre></blockquote><pre wrap="">-- Popeanga Marian DBA Oracle CNLO Romania </pre></blockquote><pre wrap="">-- Popeanga Marian DBA Oracle CNLO Romania </pre></blockquote><pre wrap=""> </pre></blockquote><br /><pre class="moz-signature" cols="$mailwrapcol">-- Popeanga Marian DBA Oracle CNLO Romania </pre><br />
> > Hello all, > > > > > > Can someone point me how can i make explain plan for a query? > > I don't want this for pgsql, but from any other tool that connects to > postgres. 7.2.x and earlier: The result of explain query is printed in STDERR, just like any other notice message. 7.3: The result is returned like a value returned from a function. regards, bhuvaneswaran
Popeanga ,
Its basically possible to get the info programatically.
see below is an example perl code that is getting the
plan data:
############### file test.pl #################
#!/usr/bin/perl
use DBI;
$dbh = DBI->connect('dbi:Pg:dbname=rmallah' , '','');
$your_query_here= 'select * from test';
$rows = $dbh -> selectall_arrayref("EXPLAIN ANALYZE $your_query_here");
foreach (@$rows) { print "$_->[0]\n";
}
$dbh -> disconnect();
#############################################
on running you get:
[rmallah@subho rmallah]$ ./test.pl
Seq Scan on test (cost=0.00..20.00 rows=1000 width=32) (actual time=0.02..0.05 rows=11 loops=1)
Total runtime: 0.19 msec
But be careful abt explain analyzing
queries that potentially modify data. (read more on docs of EXPLAIN)
we have to put the explain analyze in a transaction block which
shud eventually be rollbacked.
now that you have seen how to get lines of output from EXPLAIN using/hadling this
data is upto you application.
also note that the last like of EXPLAIN analyze carries the runtime.
hope it helps
Mallah.
-----------------------------------------
Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
Mallah,
Thanks for your info,
I runned the query with explain yesterday and it didn't work for
a user where i didn't had enough privileges i think !?
Why db didn't say anythink ?
I created a new user and a new table.
Explain for this table worked just as you said and for any other
querys.
Is there any way to know if a user has wright privileges to explain a
query ?
mallah@trade-india.com wrote:
>Popeanga ,
>
>Its basically possible to get the info programatically.
>see below is an example perl code that is getting the
>plan data:
>
>############### file test.pl #################
>
>#!/usr/bin/perl
>
>use DBI;
>$dbh = DBI->connect('dbi:Pg:dbname=rmallah' , '','');
>$your_query_here= 'select * from test';
>$rows = $dbh -> selectall_arrayref("EXPLAIN ANALYZE $your_query_here");
>foreach (@$rows) {
> print "$_->[0]\n";
>}
>$dbh -> disconnect();
>#############################################
>
>on running you get:
>
>[rmallah@subho rmallah]$ ./test.pl
>Seq Scan on test (cost=0.00..20.00 rows=1000 width=32) (actual time=0.02..0.05 rows=11 loops=1)
>Total runtime: 0.19 msec
>
>But be careful abt explain analyzing
>queries that potentially modify data. (read more on docs of EXPLAIN)
>we have to put the explain analyze in a transaction block which
>shud eventually be rollbacked.
>
>now that you have seen how to get lines of output from EXPLAIN using/hadling this
>data is upto you application.
>
>also note that the last like of EXPLAIN analyze carries the runtime.
>
>hope it helps
>Mallah.
>
>
>
>
>
>
>-----------------------------------------
>Get your free web based email at trade-india.com.
> "India's Leading B2B eMarketplace.!"
>http://www.trade-india.com/
>
>
>
>
--
Popeanga Marian
DBA Oracle
CNLO Romania
Sorry ,
I am not sure about this.
ie, if a query does not have enough previliegs to run,
will its explain be allowed?
i hope someone else can throw some light.
But i have one suggestion regarding your project
spend a *couple* of week studying docs and using postgresql.
just a quick weekend may not be enough to gather much
understanding for your project.
regds
mallah.
On Saturday 22 Mar 2003 11:41 am, Popeanga Marian wrote:
> Mallah,
>
>
> Thanks for your info,
>
>
> I runned the query with explain yesterday and it didn't work for
> a user where i didn't had enough privileges i think !?
> Why db didn't say anythink ?
>
> I created a new user and a new table.
>
> Explain for this table worked just as you said and for any other
> querys.
>
> Is there any way to know if a user has wright privileges to explain a
> query ?
>
> mallah@trade-india.com wrote:
>
> >Popeanga ,
> >
> >Its basically possible to get the info programatically.
> >see below is an example perl code that is getting the
> >plan data:
> >
> >############### file test.pl #################
> >
> >#!/usr/bin/perl
> >
> >use DBI;
> >$dbh = DBI->connect('dbi:Pg:dbname=rmallah' , '','');
> >$your_query_here= 'select * from test';
> >$rows = $dbh -> selectall_arrayref("EXPLAIN ANALYZE $your_query_here");
> >foreach (@$rows) {
> > print "$_->[0]\n";
> >}
> >$dbh -> disconnect();
> >#############################################
> >
> >on running you get:
> >
> >[rmallah@subho rmallah]$ ./test.pl
> >Seq Scan on test (cost=0.00..20.00 rows=1000 width=32) (actual time=0.02..0.05 rows=11 loops=1)
> >Total runtime: 0.19 msec
> >
> >But be careful abt explain analyzing
> >queries that potentially modify data. (read more on docs of EXPLAIN)
> >we have to put the explain analyze in a transaction block which
> >shud eventually be rollbacked.
> >
> >now that you have seen how to get lines of output from EXPLAIN using/hadling this
> >data is upto you application.
> >
> >also note that the last like of EXPLAIN analyze carries the runtime.
> >
> >hope it helps
> >Mallah.
> >
> >
> >
> >
> >
> >
> >-----------------------------------------
> >Get your free web based email at trade-india.com.
> > "India's Leading B2B eMarketplace.!"
> >http://www.trade-india.com/
> >
> >
> >
> >
>
> --
> Popeanga Marian
> DBA Oracle
> CNLO Romania
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.