Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f8Xni-0008Az-Vn for pgsql-performance@arkaria.postgresql.org; Tue, 17 Apr 2018 21:06:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f8Xnh-0005wo-L0 for pgsql-performance@arkaria.postgresql.org; Tue, 17 Apr 2018 21:06:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f8Xnh-0005wf-Av for pgsql-performance@lists.postgresql.org; Tue, 17 Apr 2018 21:06:05 +0000 Received: from sonic316-9.consmr.mail.gq1.yahoo.com ([98.137.69.33]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f8Xnb-0007Tv-VF for pgsql-performance@lists.postgresql.org; Tue, 17 Apr 2018 21:06:04 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1523999156; bh=7zE0bKVkdmrA2xSfwyaxu7XbPr5T2wsX9o6iHY4TGa4=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=DXcm58wgCzvHfpyDgaXlziAjl+ZxZD4epdYm4gL868dJBBezGRafBW5xAFUp0XDOxGbrYTWjOJx49taRUAh0tArgFSdXX4DodGbLWUYK+ObXf3wNPi9fdwEBxwWoCC3qJcvfIr5dPRO8igUH6Ip4acc/z1YMsl31zfALq51VPQG2yY6ZCj/jiqe2khSJT3G8EXIOwPKFPcNVb99VUYikp0GqkcVzgJA8juWifPLExVxjwUic8jhVVbWIc1kXuR5aNgMwRfb/YcmM+NGPflZ+E/yd0p4o3wAIs7yW91vcviyeF3cKcQowxhGmKTb9kbWBsd9A/r4gToqyfYNCtsakag== X-YMail-OSG: C3hWWGEVM1mXTJ8CSVA._lOkj8EXpOzcVni4h2vJPN1xSt5sZOQ3Hc5ni6m.aIk wqSUaPMZKh9Oo39yluq27tTMUt674iyryD2EXpX71WUUr._zsDPEgL8qM6WvwWzG4Mrv8zSc54n3 .KshtwVm0_OuTLfs8T7QUaQT5YLFgr3SOf0h5ThvLcQHh306V50Wu.9RknpuRrD1Q9hwqVgr8EoL vKopPG11N3HOmcBELuw31uEFTu43crHiosb_tLWFHuSHbH.H7h56nt.mum_vhl81Yv3MPIw_bN3M Qu5u8qDE1fbAjl6penOh1le2r_JFI0OdUoITFtz7GKJbjmeo.MgSd9Dfy0tFnJfq0r3xL0ze9T9y uZd6HhI63Pvy5A_mBJruV17F3xCjFrB6oKtimLzAb1R5EBALhlDsF.vZjF1AI.pXPG8Rl5rxMm.0 ZupXKvygNpGNpMVRqLX2ZWxUZgapglFe6EmsxCiyW1iMfPUyRLc5CDxCVLLMdZR0e Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.gq1.yahoo.com with HTTP; Tue, 17 Apr 2018 21:05:56 +0000 Date: Tue, 17 Apr 2018 21:05:54 +0000 (UTC) From: Deepak Somaiya To: Hackety Man , Pavel Stehule , Pgsql Performance , Tomas Vondra Cc: Robert Haas Message-ID: <1048175283.1009672.1523999154175@mail.yahoo.com> In-Reply-To: References: Subject: Citext Performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1009671_35203182.1523999154169" X-Mailer: WebService/1.1.11782 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.162 Safari/537.36 Content-Length: 22238 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1009671_35203182.1523999154169 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Apology for sending you emails directly but I do see you guys responding o= n email related to performance so thought of copying you folks. Folks,=C2=A0I read following (PostgreSQL: Documentation: 9.6: citext) and i= t does not hold true in my testing.. i.e citext is not performing better th= an lower.Am I missing something? help is appreciated. |=20 |=20 |=20 | | | | | |=20 | |=20 PostgreSQL: Documentation: 9.6: citext | | | "citext=C2=A0is not as efficient as=C2=A0text=C2=A0because the operator fun= ctions 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 ef= ficient than using=C2=A0lower=C2=A0to get case-insensitive matching." Here is what I have done=C2=A0 drop table test;drop table testci; CREATE TABLE test (id INTEGER PRIMARY KEY,name character varying(254));CREA= TE TABLE testci (id INTEGER PRIMARY KEY,name citext ); INSERT INTO test(id, name)SELECT generate_series(1000001,2000000), (md5(ran= dom()::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)=3Dlo= wer('f6d7d5be1d0bed1cca11540d3a2667de'); - Plan:=C2=A0=C2=A0 =C2=A0 Node Type: "Seq Scan"=C2=A0 =C2=A0 Parallel Awar= e: false=C2=A0 =C2=A0 Relation Name: "test"=C2=A0 =C2=A0 Alias: "test"=C2= =A0 =C2=A0 Startup Cost: 0.00=C2=A0 =C2=A0 Total Cost: 23334.00=C2=A0 =C2= =A0 Plan Rows: 5000=C2=A0 =C2=A0 Plan Width: 37=C2=A0 =C2=A0 Actual Startup= Time: 0.016=C2=A0 =C2=A0 Actual Total Time: 680.199=C2=A0 =C2=A0 Actual Ro= ws: 1=C2=A0 =C2=A0 Actual Loops: 1=C2=A0 =C2=A0 Filter: "(lower((name)::tex= t) =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"=C2=A0 =C2=A0 Rows Removed= by Filter: 999999=C2=A0 Planning Time: 0.045=C2=A0 Triggers:=C2=A0=C2=A0 E= xecution Time: 680.213 explain (analyze on, format yaml) select * from testci where name=3D'956d69= 2092f0b9f85f36bf2b2501f3ad'; - Plan:=C2=A0=C2=A0 =C2=A0 Node Type: "Seq Scan"=C2=A0 =C2=A0 Parallel Awar= e: false=C2=A0 =C2=A0 Relation Name: "testci"=C2=A0 =C2=A0 Alias: "testci"= =C2=A0 =C2=A0 Startup Cost: 0.00=C2=A0 =C2=A0 Total Cost: 20834.00=C2=A0 = =C2=A0 Plan Rows: 1=C2=A0 =C2=A0 Plan Width: 37=C2=A0 =C2=A0 Actual Startup= Time: 0.017=C2=A0 =C2=A0 Actual Total Time: 1184.485=C2=A0 =C2=A0 Actual R= ows: 1=C2=A0 =C2=A0 Actual Loops: 1=C2=A0 =C2=A0 Filter: "(name =3D '956d69= 2092f0b9f85f36bf2b2501f3ad'::citext)"=C2=A0 =C2=A0 Rows Removed by Filter: = 999999=C2=A0 Planning Time: 0.029=C2=A0 Triggers:=C2=A0=C2=A0 Execution Tim= e: 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)=3Dlo= wer('f6d7d5be1d0bed1cca11540d3a2667de'); - Plan:=C2=A0=C2=A0 =C2=A0 Node Type: "Bitmap Heap Scan"=C2=A0 =C2=A0 Paral= lel Aware: false=C2=A0 =C2=A0 Relation Name: "test"=C2=A0 =C2=A0 Alias: "te= st"=C2=A0 =C2=A0 Startup Cost: 187.18=C2=A0 =C2=A0 Total Cost: 7809.06=C2= =A0 =C2=A0 Plan Rows: 5000=C2=A0 =C2=A0 Plan Width: 37=C2=A0 =C2=A0 Actual = Startup Time: 0.020=C2=A0 =C2=A0 Actual Total Time: 0.020=C2=A0 =C2=A0 Actu= al Rows: 1=C2=A0 =C2=A0 Actual Loops: 1=C2=A0 =C2=A0 Recheck Cond: "(lower(= (name)::text) =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"=C2=A0 =C2=A0 R= ows Removed by Index Recheck: 0=C2=A0 =C2=A0 Exact Heap Blocks: 1=C2=A0 =C2= =A0 Lossy Heap Blocks: 0=C2=A0 =C2=A0 Plans:=C2=A0=C2=A0 =C2=A0 =C2=A0 - No= de Type: "Bitmap Index Scan"=C2=A0 =C2=A0 =C2=A0 =C2=A0 Parent Relationship= : "Outer"=C2=A0 =C2=A0 =C2=A0 =C2=A0 Parallel Aware: false=C2=A0 =C2=A0 =C2= =A0 =C2=A0 Index Name: "textlowerindex"=C2=A0 =C2=A0 =C2=A0 =C2=A0 Startup = Cost: 0.00=C2=A0 =C2=A0 =C2=A0 =C2=A0 Total Cost: 185.93=C2=A0 =C2=A0 =C2= =A0 =C2=A0 Plan Rows: 5000=C2=A0 =C2=A0 =C2=A0 =C2=A0 Plan Width: 0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 Actual Startup Time: 0.016=C2=A0 =C2=A0 =C2=A0 =C2=A0 = Actual Total Time: 0.016=C2=A0 =C2=A0 =C2=A0 =C2=A0 Actual Rows: 1=C2=A0 = =C2=A0 =C2=A0 =C2=A0 Actual Loops: 1=C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond:= "(lower((name)::text) =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"=C2=A0= Planning Time: 0.051=C2=A0 Triggers:=C2=A0=C2=A0 Execution Time: 0.035 explain (analyze on, format yaml) select * from testci where name=3D'956d69= 2092f0b9f85f36bf2b2501f3ad';=C2=A0 - Plan:=C2=A0=C2=A0 =C2=A0 Node Type: "Index Scan"=C2=A0 =C2=A0 Parallel Aw= are: false=C2=A0 =C2=A0 Scan Direction: "Forward"=C2=A0 =C2=A0 Index Name: = "citextindex"=C2=A0 =C2=A0 Relation Name: "testci"=C2=A0 =C2=A0 Alias: "tes= tci"=C2=A0 =C2=A0 Startup Cost: 0.42=C2=A0 =C2=A0 Total Cost: 8.44=C2=A0 = =C2=A0 Plan Rows: 1=C2=A0 =C2=A0 Plan Width: 37=C2=A0 =C2=A0 Actual Startup= Time: 0.049=C2=A0 =C2=A0 Actual Total Time: 0.050=C2=A0 =C2=A0 Actual Rows= : 1=C2=A0 =C2=A0 Actual Loops: 1=C2=A0 =C2=A0 Index Cond: "(name =3D '956d6= 92092f0b9f85f36bf2b2501f3ad'::citext)"=C2=A0 =C2=A0 Rows Removed by Index R= echeck: 0=C2=A0 Planning Time: 0.051=C2=A0 Triggers:=C2=A0=C2=A0 Execution = Time: 0.064 ------=_Part_1009671_35203182.1523999154169 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Apology for sending you emails directly but I do see you guys = responding on email related to performance so thought of copying you folks.=

Folks,



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

<= div>
Here is what I ha= ve 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

<= div>);

INSERT INTO test(id, name)
SELECT= generate_series(1000001,2000000), (md5(random()::text));

INSERT INTO testci(id, name)
SELECT generate_series(1,100= 0000), (md5(random()::text));


Now, = I have done sequential search

explain (analyze on,= format yaml) select * from test where lower(name)=3Dlower('f6d7d5be1d0bed1= cca11540d3a2667de');
- Plan: 
  &nbs= p; Node Type: "Seq Scan"
    Parallel Aware: false
    Relation Name: "test"
    Alias: "t= est"
    Startup Cost: 0.00
    Tot= al Cost: 23334.00
    Plan Rows: 5000
  =   Plan Width: 37
    Actual Startup Time: 0.016
    Actual Total Time: 680.199
    Ac= tual Rows: 1
    Actual Loops: 1
   = ; Filter: "(lower((name)::text) =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::tex= t)"
    Rows Removed by Filter: 999999
 = Planning Time: 0.045
  Triggers: 
  Exe= cution Time: 680.213


explain (analy= ze on, format yaml) select * from testci where name=3D'956d692092f0b9f85f36= bf2b2501f3ad';
- Plan: 
    Nod= e Type: "Seq Scan"
    Parallel Aware: false
=     Relation Name: "testci"
    Alias: "testc= i"
    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
    Fi= lter: "(name =3D '956d692092f0b9f85f36bf2b2501f3ad'::citext)"
&nb= sp;   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 adde= d index on citext and equivalent functional index (lower) on text.


CREATE INDEX textlowerindex ON test (lower(= name));
create index textindex on test(name);
<= br>

Index creation took longer with citext v/s cre= ating lower functional index.


Now h= ere comes execution with indexes

explain (analyze on, format yaml) select * from test wher= e lower(name)=3Dlower('f6d7d5be1d0bed1cca11540d3a2667de');

- Plan: 
&nb= sp;   Node Type: "Bitmap Heap Scan"
    Parallel A= ware: false
    Relation Name: "test"
  =   Alias: "test"
    Startup Cost: 187.18
    Total Cost: 7809.06
    Plan Rows: 5000<= /div>
    Plan Width: 37
    Actual Start= up Time: 0.020
    Actual Total Time: 0.020
&= nbsp;   Actual Rows: 1
    Actual Loops: 1
    Recheck Cond: "(lower((name)::text) =3D 'f6d7d5be1d0bed1cc= a11540d3a2667de'::text)"
    Rows Removed by Index Rech= eck: 0
    Exact Heap Blocks: 1
   = Lossy Heap Blocks: 0
    Plans: 
 =     - Node Type: "Bitmap Index Scan"
    &nb= sp;   Parent Relationship: "Outer"
      &nbs= p; Parallel Aware: false
        Index Name: = "textlowerindex"
        Startup Cost: 0.00
        Total Cost: 185.93
  &n= bsp;     Plan Rows: 5000
        Pl= an Width: 0
        Actual Startup Time: 0.01= 6
        Actual Total Time: 0.016
=         Actual Rows: 1
      &= nbsp; Actual Loops: 1
        Index Cond: "(l= ower((name)::text) =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"
  Planning Time: 0.051
  Triggers: 
&nb= sp; Execution Time: 0.035


=


explain (analyze on, format yaml) = select * from testci where name=3D'956d692092f0b9f85f36bf2b= 2501f3ad'; 

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


------=_Part_1009671_35203182.1523999154169--