Re: Case Insensitive

Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> wrote on 03/28/2019 07:28:53
AM:

> >
> > Ummm... Will it use an index (a BTree index)?
> >
>
> test=# explain select * from emp where ename = 'aaa';
>                      QUERY PLAN
> -----------------------------------------------------
>  Seq Scan on emp  (cost=0.00..25.88 rows=6 width=36)
>    Filter: (ename = 'aaa'::citext)
> (2 rows)
>
> test=*# set enable_seqscan to off;
> SET
> test=*# explain select * from emp where ename = 'aaa';
>                                QUERY PLAN
> ------------------------------------------------------------------------
>  Seq Scan on emp  (cost=10000000000.00..10000000025.88 rows=6 width=36)
>    Filter: (ename = 'aaa'::citext)
> (2 rows)
>
> test=*# create index emp_ename on emp(ename);
> CREATE INDEX
> test=*# explain select * from emp where ename = 'aaa';
>                               QUERY PLAN
> ----------------------------------------------------------------------
>  Index Scan using emp_ename on emp  (cost=0.13..8.14 rows=1 width=36)
>    Index Cond: (ename = 'aaa'::citext)
> (2 rows)

I used citext heavily in a past project and was quite happy with it. It
was never a source of performance issues.

Brad.