| Lists: | pgsql-hackers |
|---|
| From: | Rod Taylor <pg(at)rbt(dot)ca> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | CIText and pattern_ops |
| Date: | 2010-04-24 03:27:53 |
| Message-ID: | y2j751261b21004232027xc1979803mc20b2bcd688912c2@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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) ).
| From: | Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
|---|---|
| To: | Rod Taylor <pg(at)rbt(dot)ca> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: CIText and pattern_ops |
| Date: | 2010-04-26 09:53:28 |
| Message-ID: | 20100426185328.927E.52131E4D@oss.ntt.co.jp |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
Rod Taylor <pg(at)rbt(dot)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.27 rows=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.01 rows=1 width=64)
Filter: (c ~~ 'abc%'::citext)
(2 rows)
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | Rod Taylor <pg(at)rbt(dot)ca> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: CIText and pattern_ops |
| Date: | 2010-04-26 10:19:34 |
| Message-ID: | r2g603c8f071004260319p555336b1q8ac243101de4158b@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor <pg(at)rbt(dot)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
| From: | Thom Brown <thombrown(at)gmail(dot)com> |
|---|---|
| To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
| Cc: | Rod Taylor <pg(at)rbt(dot)ca>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: CIText and pattern_ops |
| Date: | 2010-04-26 13:40:56 |
| Message-ID: | r2qbddc86151004260640gc720531dw79cbcb21f18b3f5e@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
On 26 April 2010 11:19, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor <pg(at)rbt(dot)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
>
>
Yes, someone implementing this would be greatly appreciated, especially
since I've just started using this datatype. ;)
Thom