Re: explain

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