SPI access to PostgreSQL query plan
Hi,
Is there a way to have access to PostgreSQL query plan and/or predicates
inside a function using spi (or any other way)?
For example:
explain select * from my_func() as (code int, name varchar) where name like
'a%';
QUERY PLAN
--------------------------------------------------------------
Function Scan on my_func (cost=0.00..15.00 rows=5 width=36)
Filter: ((name)::text ~~ 'a%'::text)
I would like to know, inside the my_func function that PostgreSQL will
filter the function's returned result on the name column
with a like operator matching 'a%'. Since inside the my_func function I do
another database query, I could "pre-filter" this
query and reduce the amount of records returned.
If the whole plan cannot be exposed to spi, can only the part involved on
filtering/manipulating the records returned from
the function call?
Is the query plan saved in memory at a place where a spi function can have
access?
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.
regards, tom lane
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?
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