Thread: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.

Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.

From
Piotr Gasidło
Date:
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)

Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.

From
Richard Huxton
Date:
Piotr Gasidło wrote:
> 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?

It plans the query just once for the pl/pgsql function. That means it
doesn't know whether you are passing in a name '%foo' which can't use
the index. Since only one plan can be used it has to use a scan of the
table.

You can use EXECUTE to get plpgsql to plan the query each time it is
called. That should let it recognise that it can use the index (if it
can, of course).

--
   Richard Huxton
   Archonet Ltd

Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.

From
"Pavel Stehule"
Date:
Hello

this is known problem of prepared statements. Prepared statement has
plan built without knowledge any values and should not be optimal.

try use dynamic query and statement EXECUTE INTO

Regards
Pavel Stehule



On 10/12/2007, Piotr Gasidło <quaker@barbara.eu.org> wrote:
> 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)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.

From
"Piotr Gasidło"
Date:
2007/12/10, Piotr Gasidło <quaker@barbara.eu.org>:
> Why PL/PGSQL is unable to proper utilize
> users_user_name_unique_text_pattern_ops?

I found solution, that satisfies me (EXECUTE is a bit ugly for me).

I've replaced LIKE operator with ~=~ operator. Now PL/PGSQL function
properly uses index on SELECT.