Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.
От | Piotr Gasidło |
---|---|
Тема | Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure. |
Дата | |
Msg-id | 475D37D9.3000208@barbara.eu.org обсуждение исходный текст |
Ответы |
Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL
procedure.
(Richard Huxton <dev@archonet.com>)
Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure. ("Pavel Stehule" <pavel.stehule@gmail.com>) Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure. ("Piotr Gasidło" <quaker@barbara.eu.org>) |
Список | pgsql-performance |
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)
В списке pgsql-performance по дате отправления:
Следующее
От: Richard HuxtonДата:
Сообщение: Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.