From: Deepak Somaiya <deepsom@yahoo.com>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
To: david@kineticode.com <david@kineticode.com>
Subject: citext performance
Date: Fri, 6 Apr 2018 16:51:14 +0000 (UTC)
Message-ID: <1991635447.2385019.1523033474018@mail.yahoo.com> (raw)
References: <1991635447.2385019.1523033474018.ref@mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: deepsom@yahoo.com, pgsql-performance@lists.postgresql.org, david@kineticode.com
Subject: Re: citext performance
In-Reply-To: <1991635447.2385019.1523033474018@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox