From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Why is varchar_pattern_ops needed? |
Date: | 2014-03-03 10:06:55 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B17CCE003@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2014-03-03 15:13:16 | Re: multiple results from a function |
Previous Message | François Beausoleil | 2014-03-03 04:48:05 | Role Inheritance Without Explicit Naming? |