pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexplain
13+ messages / 5 participants
[nested] [flat]
* explain
@ 2003-03-21 05:46 Popeanga Marian <pmarian@cnlo.ro>
0 siblings, 2 replies; 13+ messages in thread
From: Popeanga Marian @ 2003-03-21 05:46 UTC (permalink / raw)
To: pgsql-sql
Hello all,
Can someone point me how can i make explain plan for a query?
--
Popeanga Marian
DBA Oracle
CNLO Romania
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 06:09 Popeanga Marian <pmarian@cnlo.ro>
parent: Popeanga Marian <pmarian@cnlo.ro>
1 sibling, 1 reply; 13+ messages in thread
From: Popeanga Marian @ 2003-03-21 06:09 UTC (permalink / raw)
To: Popeanga Marian <pmarian@cnlo.ro>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 06:21 Popeanga Marian <pmarian@cnlo.ro>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 1 reply; 13+ messages in thread
From: Popeanga Marian @ 2003-03-21 06:21 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 06:34 Roberto Mello <rmello@cc.usu.edu>
parent: Popeanga Marian <pmarian@cnlo.ro>
1 sibling, 1 reply; 13+ messages in thread
From: Roberto Mello @ 2003-03-21 06:34 UTC (permalink / raw)
To: Popeanga Marian <pmarian@cnlo.ro>; +Cc: pgsql-sql
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"
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 08:23 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Popeanga Marian <pmarian@cnlo.ro>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-21 08:23 UTC (permalink / raw)
To: Popeanga Marian <pmarian@cnlo.ro>; Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 08:27 Popeanga Marian <pmarian@cnlo.ro>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 13+ messages in thread
From: Popeanga Marian @ 2003-03-21 08:27 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Roberto Mello <rmello@cc.usu.edu>; pgsql-sql
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 ... ?
In Oracle i will do:
explain plan set statement_id=uniq_id_for_select for
select * from dba_tables where table_name=:tab
SELECT * FROM plan_table WHERE Statement_ID = this_uniq_id_for_select;
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 11:00 Popeanga Marian <pmarian@cnlo.ro>
parent: Popeanga Marian <pmarian@cnlo.ro>
0 siblings, 1 reply; 13+ messages in thread
From: Popeanga Marian @ 2003-03-21 11:00 UTC (permalink / raw)
To: Popeanga Marian <pmarian@cnlo.ro>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; Roberto Mello <rmello@cc.usu.edu>; pgsql-sql
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 ... ?
>
>
>
> In Oracle i will do:
> explain plan set statement_id=uniq_id_for_select for
> select * from dba_tables where table_name=:tab
>
> SELECT * FROM plan_table WHERE Statement_ID = this_uniq_id_for_select;
>
>
>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 12:10 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Popeanga Marian <pmarian@cnlo.ro>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-21 12:10 UTC (permalink / raw)
To: Popeanga Marian <pmarian@cnlo.ro>; +Cc: Roberto Mello <rmello@cc.usu.edu>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 12:35 Popeanga Marian <pmarian@cnlo.ro>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 13+ messages in thread
From: Popeanga Marian @ 2003-03-21 12:35 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Roberto Mello <rmello@cc.usu.edu>; pgsql-sql
Ok i'll try to explain you in detail what i am trying to accomplish.
I am working at a cross platform tool for administration (and many more
) of Oracle , Postgresql, MySql
http://cptd-proj.sourceforge.net
I don't have many knowledge as a postgresql user or dba. I have
worked with Oracle mainly.
In Oracle explain plans are stored into a special table named PLAN_TABLE
so when i issue
explain plan set statement_id=uniq_id_for_select for
select * from dba_tables where table_name=:tab
new entrys with the explain plan are inserted into this special table.
And you can select those entrys from this PLAN_TABLE and show it into a
widget.
SELECT * FROM plan_table WHERE Statement_ID = uniq_id_for_select
Now the issue i encounter is that i want to show the excution plan for
connections against PostgreSql DB too, but i don't know from where to
find this information.
I noticed that pgsql is able to explain plan for selects.
But how ?
Rajesh Kumar Mallah wrote:
>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
>>
>>
>>
>>
>>
>
>
>
--
Popeanga Marian
DBA Oracle
CNLO Romania
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 13:08 A.Bhuvaneswaran <bhuvansql@myrealbox.com>
parent: Popeanga Marian <pmarian@cnlo.ro>
0 siblings, 0 replies; 13+ messages in thread
From: A.Bhuvaneswaran @ 2003-03-21 13:08 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-21 16:32 mallah@trade-india.com
0 siblings, 1 reply; 13+ messages in thread
From: mallah@trade-india.com @ 2003-03-21 16:32 UTC (permalink / raw)
To: pmarian@cnlo.ro; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-22 06:11 Popeanga Marian <pmarian@cnlo.ro>
parent: mallah@trade-india.com
0 siblings, 1 reply; 13+ messages in thread
From: Popeanga Marian @ 2003-03-22 06:11 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: explain
@ 2003-03-22 08:45 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Popeanga Marian <pmarian@cnlo.ro>
0 siblings, 0 replies; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-22 08:45 UTC (permalink / raw)
To: Popeanga Marian <pmarian@cnlo.ro>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-03-22 08:45 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-21 05:46 explain Popeanga Marian <pmarian@cnlo.ro>
2003-03-21 06:09 ` Popeanga Marian <pmarian@cnlo.ro>
2003-03-21 13:08 ` A.Bhuvaneswaran <bhuvansql@myrealbox.com>
2003-03-21 06:34 ` Roberto Mello <rmello@cc.usu.edu>
2003-03-21 06:21 ` Popeanga Marian <pmarian@cnlo.ro>
2003-03-21 08:23 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-21 08:27 ` Popeanga Marian <pmarian@cnlo.ro>
2003-03-21 11:00 ` Popeanga Marian <pmarian@cnlo.ro>
2003-03-21 12:10 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-21 12:35 ` Popeanga Marian <pmarian@cnlo.ro>
2003-03-21 16:32 Re: explain mallah@trade-india.com
2003-03-22 06:11 ` Popeanga Marian <pmarian@cnlo.ro>
2003-03-22 08:45 ` Rajesh Kumar Mallah <mallah@trade-india.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