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
