Thread: Immutable function with bind value
Hi, i've discovered something kind of weird while developing my app... I was trying to fetch some records in a table using a function immutable. In my interface it was really slow and while i was in a psql it was really fast ... After some research i've found out that it was caused by the bind parameter "forcing" the immutable function to execute each time my query gets a record. while i know that the value can't be known at planning time the difference between the binded / not binded is quite enormous... i've isolated a test case here.... https://gist.github.com/e93792540cb3a68054c9 (it happens at least in pg 8.4 and 9.1.2) What do you think about it... bug? feature? how can i avoid it? Thanks
On 20/01/2012 12:43, Brice Maron wrote: > Hi, > > i've discovered something kind of weird while developing my app... > I was trying to fetch some records in a table using a function immutable. > In my interface it was really slow and while i was in a psql it was > really fast ... > > After some research i've found out that it was caused by the bind > parameter "forcing" the immutable function to execute each time my > query gets a record. > > while i know that the value can't be known at planning time the > difference between the binded / not binded is quite enormous... > > i've isolated a test case here.... > > https://gist.github.com/e93792540cb3a68054c9 > > (it happens at least in pg 8.4 and 9.1.2) > > > What do you think about it... bug? feature? how can i avoid it? How about: PREPARE test (varchar) as select * from test where a = $1; EXECUTE test(test_immutable(1)); Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/
Thanks Matteo for your answer...
but.. this is only a usecase... i'm currently using thing like this in
a php app,
so i'm doing kind of a as
select * from test where a = test_immutable(?);
then
execute('var');
unfortunately there, i can't to an execute(test_immutable('var'))
for now i've avoid using bind for those queries but i didn't like it....
Cheers,
Brice
On Fri, Jan 20, 2012 at 15:28, Matteo Beccati <php@beccati.com> wrote:
> On 20/01/2012 12:43, Brice Maron wrote:
>> Hi,
>>
>> i've discovered something kind of weird while developing my app...
>> I was trying to fetch some records in a table using a function immutable.
>> In my interface it was really slow and while i was in a psql it was
>> really fast ...
>>
>> After some research i've found out that it was caused by the bind
>> parameter "forcing" the immutable function to execute each time my
>> query gets a record.
>>
>> while i know that the value can't be known at planning time the
>> difference between the binded / not binded is quite enormous...
>>
>> i've isolated a test case here....
>>
>> https://gist.github.com/e93792540cb3a68054c9
>>
>> (it happens at least in pg 8.4 and 9.1.2)
>>
>>
>> What do you think about it... bug? feature? how can i avoid it?
>
> How about:
>
> PREPARE test (varchar) as select * from test where a = $1;
> EXECUTE test(test_immutable(1));
>
>
> Cheers
> --
> Matteo Beccati
>
> Development & Consulting - http://www.beccati.com/