Re: Case Insensitive

Am 28.03.19 um 09:33 schrieb Sameer Kumar:
>
>     test=*# select * from emp where ename = 'aaa';
>       eid | ename
>     -----+-------
>         1 | aaa
>         2 | AAA
>     (2 rows)
>
>
>
> 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)

test=*#


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com