Thread: CIText and pattern_ops

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%';

This, of course, is equivalent to ILIKE 'Foo%' which does not appear
to be indexable without using a functional index (
lower(citext_column) ).

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

On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor <pg@rbt.ca> wrote:
> Is there any particular reason why the citext module doesn't have
> citext_pattern_ops operator family?

You forgot to send in the patch.  :-)

...Robert

Just picking up a dropped thread, does anyone have a patch for this?
 http://archives.postgresql.org/pgsql-hackers/2010-04/msg01191.php

Best,

David