Why is varchar_pattern_ops needed?

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Why is varchar_pattern_ops needed?
Дата
Msg-id A737B7A37273E048B164557ADEF4A58B17CCE003@ntex2010i.host.magwien.gv.at
обсуждение исходный текст
Ответы Re: Why is varchar_pattern_ops needed?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
I understand the difference between "*_ops" and "*_pattern_ops".

But look at the following:

CREATE TABLE test (v varchar(30));
CREATE INDEX test_v_ind ON test (v varchar_pattern_ops);
CREATE INDEX test_t_ind ON test (v text_pattern_ops);

SET enable_seqscan = off;

EXPLAIN VERBOSE SELECT * FROM test WHERE v ~<~ 'mama';

                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on laurenz.test  (cost=6.09..19.21 rows=250 width=78)
   Output: v
   Recheck Cond: ((test.v)::text ~<~ 'mama'::text)
   ->  Bitmap Index Scan on test_t_ind  (cost=0.00..6.03 rows=250 width=0)
         Index Cond: ((test.v)::text ~<~ 'mama'::text)
(5 rows)

DROP INDEX test_t_ind;

EXPLAIN VERBOSE SELECT * FROM test WHERE v ~<~ 'mama';

                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on laurenz.test  (cost=6.09..19.21 rows=250 width=78)
   Output: v
   Recheck Cond: ((test.v)::text ~<~ 'mama'::text)
   ->  Bitmap Index Scan on test_v_ind  (cost=0.00..6.03 rows=250 width=0)
         Index Cond: ((test.v)::text ~<~ 'mama'::text)
(5 rows)

Obviously both indexes can be used.

It seems like internally, only text is used anyway; there is not a single
system operator that takes "varchar" as argument:

SELECT count(*) FROM pg_operator WHERE oprleft = 'varchar'::regtype;

 count
-------
     0
(1 row)

Now my question is:
Is there anything that "varchar_pattern_ops" is needed for that "text_pattern_ops" cannot provide?

In other words:
What were the consequences if I did
DELETE FROM pg_opclass WHERE opcname = 'varchar_pattern_ops';

Yours,
Laurenz Albe

В списке pgsql-general по дате отправления:

Предыдущее
От: François Beausoleil
Дата:
Сообщение: Role Inheritance Without Explicit Naming?
Следующее
От: Vincent Veyron
Дата:
Сообщение: Re: Statistically significant poll results