Rod Taylor <pg@rbt.ca> wrote:
> Is there any particular reason why the citext module doesn't have
> citext_pattern_ops operator family?
>
> Specifically, I wish to index for this type of query:
>
> ... WHERE citext_column LIKE 'Foo%';
I think it is a reasonable suggestion.
=# \d tbl Table "public.tbl"Column | Type | Modifiers
--------+--------+-----------t | text |c | citext |
Indexes: "tbl_c_idx" btree (c) "tbl_t_idx" btree (t)
=# SET enable_seqscan = off;
SET
=# EXPLAIN SELECT * FROM tbl WHERE t LIKE 'abc%'; QUERY PLAN
----------------------------------------------------------------------Index Scan using tbl_t_idx on tbl
(cost=0.00..8.27rows=1 width=64) Index Cond: ((t >= 'abc'::text) AND (t < 'abd'::text)) Filter: (t ~~ 'abc%'::text)
(3 rows)
=# EXPLAIN SELECT * FROM tbl WHERE c LIKE 'abc%'; QUERY PLAN
------------------------------------------------------------------------Seq Scan on tbl
(cost=10000000000.00..10000000001.01rows=1 width=64) Filter: (c ~~ 'abc%'::citext)
(2 rows)
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
