BUG #11671: UNACCENT call with constant arg evaluated for each row
The following bug has been logged on the website:
Bug reference: 11671
Logged by: Thomas
Email address: t.chaumeny@gmail.com
PostgreSQL version: 9.3.5
Operating system: OS X 10.9.4
Description:
Hi,
I noticed that filtering on a constant condition using UNACCENT lead to
significantly slower queries than expected :
1 - First query without UNACCENT:
EXPLAIN ANALYZE SELECT first_name, last_name FROM user WHERE first_name =
'a';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on user (cost=0.00..2509.61 rows=1 width=32) (actual
time=19.814..19.814 rows=0 loops=1)
Filter: ((first_name)::text = 'a'::text)
Rows Removed by Filter: 31409
Total runtime: 19.870 ms
(4 rows)
2 - Second with UNACCENT (>4 times slower):
mydb=# EXPLAIN ANALYZE SELECT first_name, last_name FROM user WHERE
first_name = UNACCENT('a');
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on user (cost=0.00..2588.14 rows=1 width=32) (actual
time=88.913..88.913 rows=0 loops=1)
Filter: ((first_name)::text = unaccent('a'::text))
Rows Removed by Filter: 31409
Total runtime: 88.969 ms
(4 rows)
Time: 89,767 ms
3 - Third with "SELECT UNACCENT(...)" — which seems to force constant
evaluation :
mydb=# EXPLAIN ANALYZE SELECT first_name, last_name FROM user WHERE
first_name = (SELECT UNACCENT('a'));
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on user (cost=0.01..2509.62 rows=1 width=32) (actual
time=16.875..16.875 rows=0 loops=1)
Filter: ((first_name)::text = $0)
Rows Removed by Filter: 31409
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.024..0.025
rows=1 loops=1)
Total runtime: 16.995 ms
(6 rows)
Since UNACCENT function is defined as STABLE, I would expect its result to
be evaluated only once in the second query.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
t.chaumeny@gmail.com writes:
Since UNACCENT function is defined as STABLE, I would expect its result to
be evaluated only once in the second query.
The system does not promise any such thing for stable functions.
STABLE is a marker that it is *safe* to evaluate the function fewer
times than naive SQL semantics would suggest; it is not a requirement.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
I understand that this is not a requirement for a STABLE function. Still,
from the user point of view, I think it's unfortunate — and unexpected for
those who don't know about UNACCENT not being defined as IMMUTABLE — that
UNACCENT calls will make some queries very slow (I have another table where
the execution time goes from < 400ms to 4s+ because of that).
Regards,
Thomas
On Tue, Oct 14, 2014 at 8:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
t.chaumeny@gmail.com writes:
Since UNACCENT function is defined as STABLE, I would expect its result
to
be evaluated only once in the second query.
The system does not promise any such thing for stable functions.
STABLE is a marker that it is *safe* to evaluate the function fewer
times than naive SQL semantics would suggest; it is not a requirement.regards, tom lane
Tom Lane-2 wrote
t.chaumeny@
writes:
Since UNACCENT function
[...] than naive SQL [...]
Was that intentional... ;)
Given the planner knows its safe, and has to do a sequential scan and so
evaluate the expression many times, the question becomes why then doesn't it
choose that which is, in reality, the faster plan?
Reading 9.3@35.6
http://www.postgresql.org/docs/9.3/static/xfunc-volatility.html
I take it had the plan used an index instead of a sequential scan the
optimization would have occurred...
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11671-UNACCENT-call-with-constant-arg-evaluated-for-each-row-tp5823010p5823070.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs