Message-ID: <475D37D9.3000208@barbara.eu.org>
Date: Mon, 10 Dec 2007 13:58:01 +0100
From: =?ISO-8859-2?Q?Piotr_Gasid=B3o?= <quaker@barbara.eu.org>
User-Agent: Thunderbird 2.0.0.6 (X11/20071022)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

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)