From: | t(dot)chaumeny(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #11671: UNACCENT call with constant arg evaluated for each row |
Date: | 2014-10-14 09:12:21 |
Message-ID: | 20141014091221.25461.16786@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 11671
Logged by: Thomas
Email address: t(dot)chaumeny(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-10-14 18:40:30 | Re: BUG #11671: UNACCENT call with constant arg evaluated for each row |
Previous Message | Inoue, Hiroshi | 2014-10-14 06:46:08 | Re: [BUGS] BUG #11608: ODBC driver crashes after wrong password entered |