From: Piotr Gasidło <quaker@barbara.eu.org>
To: pgsql-performance@postgresql.org
Subject: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.
Date: Mon, 10 Dec 2007 13:58:01 +0100
Message-ID: <475D37D9.3000208@barbara.eu.org> (raw)
Hello,
I've created table:
quaker=> \d users
Table "public.users"
Column | Type | Modifiers
-----------+-------------------+----------------------------------------------------
id | integer | not null default
nextval('users_id_seq'::regclass)
user_name | character varying | not null
extra | integer |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_user_name_unique_text_ops" UNIQUE, btree (user_name text_ops)
"users_user_name_unique_text_pattern_ops" btree (user_name
text_pattern_ops)
Filled with random data (100k records).
I do simple queries using above indexes (asking for existing record).
explain analyze select id from users where user_name = 'quaker';
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using users_user_name_unique_text_ops on users
(cost=0.00..8.28 rows=1 width=4) (actual time=0.040..0.043 rows=1 loops=1)
Index Cond: ((user_name)::text = 'quaker'::text)
Total runtime: 0.084 ms
(3 rows)
explain analyze select id from users where user_name like 'quaker';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using users_user_name_unique_text_pattern_ops on users
(cost=0.00..8.28 rows=1 width=4) (actual time=0.022..0.024 rows=1 loops=1)
Index Cond: ((user_name)::text ~=~ 'quaker'::text)
Filter: ((user_name)::text ~~ 'quaker'::text)
Total runtime: 0.050 ms
(4 rows)
Everything looks fine.
Now, I've created PL/PGSQL function:
create or replace function user_login(
_v_user_name varchar
) returns integer as $$
declare
_i_user_id integer;
begin
select id into _i_user_id from users where user_name = _v_user_name
limit 1;
if found then
return _i_user_id;
end if;
return -1;
end;
$$ language plpgsql security definer;
As shown above, I use "=" operator, which should use
users_user_name_unique_text_ops index:
explain analyze select user_login('quaker');
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.320..0.322
rows=1 loops=1)
Total runtime: 0.340 ms
(2 rows)
Some performance loss, but OK. Now I've changed "=" into "LIKE" to use
users_user_name_unique_text_pattern_ops index and rerun query:
explain analyze select user_login('quaker');
QUERY PLAN
--------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=41.606..41.608
rows=1 loops=1)
Total runtime: 41.629 ms
(2 rows)
Second run give 61.061 ms. So no improvements.
Why PL/PGSQL is unable to proper utilize
users_user_name_unique_text_pattern_ops?
quaker=> select version();
version
----------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.5 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3
20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)
(1 row)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: quaker@barbara.eu.org
Subject: Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.
In-Reply-To: <475D37D9.3000208@barbara.eu.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox