From: Popeanga Marian <pmarian@cnlo.ro>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: Roberto Mello <rmello@cc.usu.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: explain
Date: Fri, 21 Mar 2003 14:35:16 +0200
Message-ID: <3E7B0704.9020305@cnlo.ro> (raw)
References: <3E7AA719.2090507@cnlo.ro>
<3E7ACD00.6050702@cnlo.ro>
<3E7AF0CD.10103@cnlo.ro>
<200303211740.27016.mallah@trade-india.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pmarian@cnlo.ro, mallah@trade-india.com, rmello@cc.usu.edu
Subject: Re: explain
In-Reply-To: <3E7B0704.9020305@cnlo.ro>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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