citext performance

Folks, I read following (PostgreSQL: Documentation: 9.6: citext) and it does not hold true in my testing.. i.e citext is not performing better than lower.Am I missing something? help is appreciated.

|
|
|
| | |

|

|
|
| |
PostgreSQL: Documentation: 9.6: citext

|

|

|

"citext is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. It is, however, slightly more efficient than using lower to get case-insensitive matching."

Here is what I have done 
drop table test;drop table testci;
CREATE TABLE test (id INTEGER PRIMARY KEY,name character varying(254));CREATE TABLE testci (id INTEGER PRIMARY KEY,name citext
);
INSERT INTO test(id, name)SELECT generate_series(1000001,2000000), (md5(random()::text));
INSERT INTO testci(id, name)SELECT generate_series(1,1000000), (md5(random()::text));

Now, I have done sequential search
explain (analyze on, format yaml) select * from test where lower(name)=lower('f6d7d5be1d0bed1cca11540d3a2667de');
- Plan:     Node Type: "Seq Scan"    Parallel Aware: false    Relation Name: "test"    Alias: "test"    Startup Cost: 0.00    Total Cost: 23334.00    Plan Rows: 5000    Plan Width: 37    Actual Startup Time: 0.016    Actual Total Time: 680.199    Actual Rows: 1    Actual Loops: 1    Filter: "(lower((name)::text) = 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"    Rows Removed by Filter: 999999  Planning Time: 0.045  Triggers:   Execution Time: 680.213

explain (analyze on, format yaml) select * from testci where name='956d692092f0b9f85f36bf2b2501f3ad';
- Plan:     Node Type: "Seq Scan"    Parallel Aware: false    Relation Name: "testci"    Alias: "testci"    Startup Cost: 0.00    Total Cost: 20834.00    Plan Rows: 1    Plan Width: 37    Actual Startup Time: 0.017    Actual Total Time: 1184.485    Actual Rows: 1    Actual Loops: 1    Filter: "(name = '956d692092f0b9f85f36bf2b2501f3ad'::citext)"    Rows Removed by Filter: 999999  Planning Time: 0.029  Triggers:   Execution Time: 1184.496

You can see sequential searches with lower working twice as fast as citext.
Now I added index on citext and equivalent functional index (lower) on text.

CREATE INDEX textlowerindex ON test (lower(name));
create index textindex on test(name);

Index creation took longer with citext v/s creating lower functional index.

Now here comes execution with indexes
explain (analyze on, format yaml) select * from test where lower(name)=lower('f6d7d5be1d0bed1cca11540d3a2667de');

- Plan:     Node Type: "Bitmap Heap Scan"    Parallel Aware: false    Relation Name: "test"    Alias: "test"    Startup Cost: 187.18    Total Cost: 7809.06    Plan Rows: 5000    Plan Width: 37    Actual Startup Time: 0.020    Actual Total Time: 0.020    Actual Rows: 1    Actual Loops: 1    Recheck Cond: "(lower((name)::text) = 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"    Rows Removed by Index Recheck: 0    Exact Heap Blocks: 1    Lossy Heap Blocks: 0    Plans:       - Node Type: "Bitmap Index Scan"        Parent Relationship: "Outer"        Parallel Aware: false        Index Name: "textlowerindex"        Startup Cost: 0.00        Total Cost: 185.93        Plan Rows: 5000        Plan Width: 0        Actual Startup Time: 0.016        Actual Total Time: 0.016        Actual Rows: 1        Actual Loops: 1        Index Cond: "(lower((name)::text) = 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"  Planning Time: 0.051  Triggers:   Execution Time: 0.035

explain (analyze on, format yaml) select * from test where lower(name)=lower('f6d7d5be1d0bed1cca11540d3a2667de');

- Plan:     Node Type: "Index Scan"    Parallel Aware: false    Scan Direction: "Forward"    Index Name: "citextindex"    Relation Name: "testci"    Alias: "testci"    Startup Cost: 0.42    Total Cost: 8.44    Plan Rows: 1    Plan Width: 37    Actual Startup Time: 0.049    Actual Total Time: 0.050    Actual Rows: 1    Actual Loops: 1    Index Cond: "(name = '956d692092f0b9f85f36bf2b2501f3ad'::citext)"    Rows Removed by Index Recheck: 0  Planning Time: 0.051  Triggers:   Execution Time: 0.064

Deepak