Thread: 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" <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.
"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