Обсуждение: SPI access to PostgreSQL query plan

Hi,<br /><br />Is there a way to have access to PostgreSQL query plan and/or predicates inside a function using spi (or
anyother way)?<br />For example:<br /><br />explain select * from my_func() as (code int, name varchar) where name like
'a%';<br />                          QUERY PLAN<br />--------------------------------------------------------------<br
/> FunctionScan on my_func  (cost=0.00..15.00 rows=5 width=36)<br />   Filter: ((name)::text ~~ 'a%'::text) <br /><br
/>Iwould like to know, inside the my_func function that PostgreSQL will filter the function's returned result on the
namecolumn <br />with a like operator matching 'a%'. Since inside the my_func function I do another database query, I
could"pre-filter" this <br />query and reduce the amount of records returned.<br /><br />If the whole plan cannot be
exposedto spi, can only the part involved on filtering/manipulating the records returned from<br /> the function
call?<br/><br />Is the query plan saved in memory at a place where a spi function can have access? <br /><br
/>Regards,<br/><br />Cristiano Duarte.<br /><br /> 

"Cristiano Duarte" <cunha17@gmail.com> writes:
> Is there a way to have access to PostgreSQL query plan and/or predicates
> inside a function using spi (or any other way)?

No.
        regards, tom lane

2007/9/17, Tom Lane <tgl@sss.pgh.pa.us>:Hi Tom,

"No" means: there is no way since the query plan is stored in a secret/safe/protected/non-visible/fort-knox like place :)
or it means, there is no friendly way to do it with spi or casual c language programming?

Regards,

Cristiano Duarte.

Cristiano Duarte wrote:
> 2007/9/17, Tom Lane <tgl@sss.pgh.pa.us>:
>> "Cristiano Duarte" <cunha17@gmail.com> writes:
>>> Is there a way to have access to PostgreSQL query plan and/or predicates 
>>> inside a function using spi (or any other way)?
>> No.
>> 
>> Hi Tom,
> 
> "No" means: there is no way since the query plan is stored in a 
> secret/safe/protected/non-visible/fort-knox like place :) or it means, there
> is no friendly way to do it with spi or casual c language programming?

"No" as in: You function is not told by the executor which filters are applied
to it's results, and since it might be called multiple times within a query you
cannot figure that out yourself, even if you somehow got hold of the currently
executed plan.

So unless you start to hack the executor in serious ways, you'll either have to
pass the filter condition manually to your function, or live with it producing
unnecessary output rows.

Thats only holds true for functions in languages other than pl/sql (Which is
*not* the same as pl/pgsql) - SQL functions can be inlined by the executor, and
then are subject to the usual optimizations. (So they essentially behave like
views).

greetings, Florian Pflug

On Sep 17, 2007, at 19:46 , Florian G. Pflug wrote:

> Thats only holds true for functions in languages other than pl/sql  
> (Which is
> *not* the same as pl/pgsql) - SQL functions can be inlined by the  
> executor, and
> then are subject to the usual optimizations. (So they essentially  
> behave like
> views).

AIUI, the stress is on the *can*, with a meaning of "may", right? Not  
all SQL functions can be inlined.

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:
> AIUI, the stress is on the *can*, with a meaning of "may", right? Not  
> all SQL functions can be inlined.

In particular, I think the OP was thinking of a function returning set,
which we currently don't inline at all.  I believe this is doable, but
perhaps not entirely trivial.  I seem to recall looking into it a year
or two ago, and hitting some roadblocks that I can't remember the
details of ...
        regards, tom lane