X-Original-To: pgsql-sql@postgresql.org Received: from ns.cnlo.ro (cnlo.ro [193.231.112.154]) by postgresql.org (Postfix) with ESMTP id 49108474E44 for ; Fri, 21 Mar 2003 07:38:32 -0500 (EST) Received: from cnlo.ro (marian.cnlo.ro [192.168.10.64]) by ns.cnlo.ro (8.12.8/8.12.5) with ESMTP id h2LCa9nJ006965; Fri, 21 Mar 2003 14:36:09 +0200 Message-ID: <3E7B0704.9020305@cnlo.ro> Date: Fri, 21 Mar 2003 14:35:16 +0200 From: Popeanga Marian User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Rajesh Kumar Mallah Cc: Roberto Mello , pgsql-sql@postgresql.org Subject: Re: explain References: <3E7AA719.2090507@cnlo.ro> <3E7ACD00.6050702@cnlo.ro> <3E7AF0CD.10103@cnlo.ro> <200303211740.27016.mallah@trade-india.com> Content-Type: multipart/alternative; boundary="------------060500070708060409040607" X-RAVMilter-Version: 8.4.1(snapshot 20020919) (ns.cnlo.ro) X-Archive-Number: 200303/494 X-Sequence-Number: 12533 --------------060500070708060409040607 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 --------------060500070708060409040607 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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

--------------060500070708060409040607--