[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Immutable function with bind value
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Immutable function with bind value
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 p...@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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Immutable function with bind value
On Jan 20, 2012, at 6:15, Brice Maron bma...@gmail.com 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? Thanks While you prove the behavior exists your test case is not a realistic example of why you would do such a thing. I would have to say that I'd expect your query to execute the function once and cache the result for the remainder of the statement. To that end have you tried defining it as a STABLE function instead of immutable? In the real use-case what is it you are trying to accomplish? You might try using a WITH clause to resolve your function call and then use the result in the main query. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Immutable function with bind value
On Fri, Jan 20, 2012 at 16:00, David Johnston pol...@yahoo.com wrote:
On Jan 20, 2012, at 6:15, Brice Maron bma...@gmail.com 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?
Thanks
While you prove the behavior exists your test case is not a realistic example
of why you would do such a thing.
I would have to say that I'd expect your query to execute the function once
and cache the result for the remainder of the statement. To that end have you
tried defining it as a STABLE function instead of immutable?
In the real use-case what is it you are trying to accomplish?
You might try using a WITH clause to resolve your function call and then use
the result in the main query.
David J.
Hi,
i know my code is not a real world thing but i tried to isolate the problem...
Here is another piece that look more real and close to the problem i have...
https://gist.github.com/d83a9c5436d7cb8cebec
the taxonomy table has 300 000 records and the difference between
name_normalized like normalize(?) || '%'
and
name_normalized like normalize('my Taxa') || '%'
is really huge!
Thanks for the help
Brice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Immutable function with bind value
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Brice Maron
Sent: Friday, January 20, 2012 1:26 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Immutable function with bind value
On Fri, Jan 20, 2012 at 16:00, David Johnston pol...@yahoo.com wrote:
On Jan 20, 2012, at 6:15, Brice Maron bma...@gmail.com 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?
Thanks
While you prove the behavior exists your test case is not a realistic
example of why you would do such a thing.
I would have to say that I'd expect your query to execute the function
once and cache the result for the remainder of the statement. To that end
have you tried defining it as a STABLE function instead of immutable?
In the real use-case what is it you are trying to accomplish?
You might try using a WITH clause to resolve your function call and then
use the result in the main query.
David J.
Hi,
i know my code is not a real world thing but i tried to isolate the
problem...
Here is another piece that look more real and close to the problem i have...
https://gist.github.com/d83a9c5436d7cb8cebec
the taxonomy table has 300 000 records and the difference between
name_normalized like normalize(?) || '%'
and
name_normalized like normalize('my Taxa') || '%'
is really huge!
Thanks for the help
Brice
--
Brice,
Have you tried an SQL language function?
OR, like I said before:
WITH input_val AS (
SELECT normalize(?) || '%' AS check_value
)
SELECT * FROM taxonomy CROSS JOIN input_val WHERE name_normalized LIKE
check_value;
Yes it is trial-and-error but at least it's something since I don't know the
correct solution (if there is one) and no one else has chimed in yet.
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Immutable function with bind value
Hi Brice,
I think You are right, problem is just in php prepare/bindvalue
So it should be avoided...
I guess the reason you like to use bindvalue is safety in SQL injection
problem...
what should be handled on some way what depends on concrete case...
But far as I am aware string as input parametar of an function is safe
enough...
Kind Regards,
Misa
Sent from my Windows Phone
From: Brice Maron
Sent: 20/01/2012 19:28
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Immutable function with bind value
On Fri, Jan 20, 2012 at 16:00, David Johnston pol...@yahoo.com wrote:
On Jan 20, 2012, at 6:15, Brice Maron bma...@gmail.com 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?
Thanks
While you prove the behavior exists your test case is not a realistic example
of why you would do such a thing.
I would have to say that I'd expect your query to execute the function once
and cache the result for the remainder of the statement. To that end have you
tried defining it as a STABLE function instead of immutable?
In the real use-case what is it you are trying to accomplish?
You might try using a WITH clause to resolve your function call and then use
the result in the main query.
David J.
Hi,
i know my code is not a real world thing but i tried to isolate the problem...
Here is another piece that look more real and close to the problem i have...
https://gist.github.com/d83a9c5436d7cb8cebec
the taxonomy table has 300 000 records and the difference between
name_normalized like normalize(?) || '%'
and
name_normalized like normalize('my Taxa') || '%'
is really huge!
Thanks for the help
Brice
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
