Re: CIText and pattern_ops

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