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 1f4Uds-0008S2-5n for pgsql-performance@arkaria.postgresql.org; Fri, 06 Apr 2018 16:55:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f4Udr-0000UQ-DC for pgsql-performance@arkaria.postgresql.org; Fri, 06 Apr 2018 16:55:11 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f4UaA-0003EO-Nu for pgsql-performance@lists.postgresql.org; Fri, 06 Apr 2018 16:51:23 +0000 Received: from sonic311-22.consmr.mail.gq1.yahoo.com ([98.137.65.203]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f4Ua7-0001F8-Be for pgsql-performance@lists.postgresql.org; Fri, 06 Apr 2018 16:51:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1523033476; bh=ahtJCugc8NOw1SQhjGIjtO9gtOpZWa91kMkn+xRFsrU=; h=Date:From:To:Subject:References:From:Subject; b=gx1KdXNYgdK17PEiIznDJ2lpV8yUoySOcrQ+xpxe9sW5bAUNrnzlfU3XkWBPpPx+Vu+G2bFbZjHvWh5xiat70BmwlnmT2tgMUslrwrl+ld/ltsVR2qqmh2g9WphdhF2O6edHXwmwZVLQ6MnJs79PmK0vAyAUkjNS+2n5wh00B5RDhUDZ9M0SQ6uzVDS4J+OF+yKO7Q1bkgxqfttr6Ov+qZ2YoEw5YoLxcLng3EimJAVHukVpOqRcmquAZz0Nj4vcks7yjn31mE54rgeIfCMYgMjBiaeS8C8l9oxr+g0Fcl+NhNKzm7sO3skMn2xClTepuIvBexvsGT8f33tJz1mJBA== X-YMail-OSG: FldQGbUVM1mo7JrX8GsxfkThZYHPIrvm_bxKM5FvKVBQ.Tbe.SxceYX8gO4jG7J 9O7coyrToK3p_4AVm322z606ayE_WFSX0dO1xCXEQ.5agl7gEE1BCgSz9T7LcWJ1LL_kKhXYCAoD iKkZbOp2WjTPSaM_PNRAarf534aMpb0qIAle2pXOinGGR_90kMy4y4A3aJTw9wXUwDgGN88A3Po6 nACO5nlZI7Zumt.t4mRahQuISL_E89_sXB9Fmv2ODpv0LnZMcTOYGl_TpvKiTTiyFfocxJXZ2xXB 3ksEyJzRgEWzujLISWCXhOrFxkSQOIFJSxzAit_oydrPAqdtiz_VMfUO.W5ItMObMnc.vvgTK.AO LfaMDLa3n6UFG5JVxxnyg.HqPzsGuLxZSpQxe.v42K2sQYoflNPFcSi6rZMAmhOG7G8fqYI58NqP 5k2APHYNiJUDUqGTFw8VkX_wJgwoqoKKWd6FpP2QLCGY3GhMJ9RfOYw4zwqG7HfNo Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.gq1.yahoo.com with HTTP; Fri, 6 Apr 2018 16:51:16 +0000 Date: Fri, 6 Apr 2018 16:51:14 +0000 (UTC) From: Deepak Somaiya To: "pgsql-performance@lists.postgresql.org" , "david@kineticode.com" Message-ID: <1991635447.2385019.1523033474018@mail.yahoo.com> Subject: citext performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2385018_1558798623.1523033474009" References: <1991635447.2385019.1523033474018.ref@mail.yahoo.com> X-Mailer: WebService/1.1.11701 YMailNorrin Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.162 Safari/537.36 Content-Length: 19761 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2385018_1558798623.1523033474009 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 Par= allel Aware: false=C2=A0 =C2=A0 Relation Name: "test"=C2=A0 =C2=A0 Alias: "= test"=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 test where lower(name)=3Dl= ower('f6d7d5be1d0bed1cca11540d3a2667de'); - Plan:=C2=A0=C2=A0 =C2=A0 Node Type: "Index Scan"=C2=A0 =C2=A0 Parallel A= ware: 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: "te= stci"=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 Deepak ------=_Part_2385018_1558798623.1523033474009 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Folks,
 I read follow= ing (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 s= omething? help is appreciated.




"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 l= ower case for comparisons. It is, however, slightly more efficient than usi= ng lower to get case-inse= nsitive matching."


<= span>Here is what I have done 

<= span>
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 gene= rate_series(1000001,2000000), (md5(random()::text));

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


Now, I hav= e done sequential search

explain (analyze on= , format yaml) select * from test where lower(name)=3Dlower('f6d7d5be1d0bed= 1cca11540d3a2667de');
- Plan: 
    Node Type: "Seq Scan"
    Parall= el Aware: false
    Relation Name: "test"
&nb= sp;   Alias: "test"
    Startup Cost: 0.00
    Total Cost: 23334.00
    Plan Rows: 50= 00
    Plan Width: 37
    Actual St= artup Time: 0.016
    Actual Total Time: 680.199
<= div>    Actual Rows: 1
    Actual Loops: 1
    Filter: "(lower((name)::text) =3D 'f6d7d5be1d0bed1cca= 11540d3a2667de'::text)"
    Rows Removed by Filter: 999= 999
  Planning Time: 0.045
  Triggers: <= /div>
  Execution Time: 680.213


explain (analyze on, format yaml) select * from testci where na= me=3D'956d692092f0b9f85f36bf2b2501f3ad';
- = Plan: 
    Node Type: "Seq Scan"
  =   Parallel Aware: false
    Relation Name: "testci= "
    Alias: "testci"
    Startup C= ost: 0.00
    Total Cost: 20834.00
  &nb= sp; Plan Rows: 1
    Plan Width: 37
  &n= bsp; Actual Startup Time: 0.017
    Actual Total Time: = 1184.485
    Actual Rows: 1
    Act= ual Loops: 1
    Filter: "(name =3D '956d692092f0b9f85f= 36bf2b2501f3ad'::citext)"
    Rows Removed by Filter: 9= 99999
  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 cite= xt 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 cr= eating lower functional index.


Now = here comes execution with indexes

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

<= /div>
- Plan: 
    Node Typ= e: "Bitmap Heap Scan"
    Parallel Aware: false
    Relation Name: "test"
    Alias: "test= "
    Startup Cost: 187.18
    Tota= l Cost: 7809.06
    Plan Rows: 5000
  &n= bsp; Plan Width: 37
    Actual Startup Time: 0.020
    Actual Total Time: 0.020
    Actual= Rows: 1
    Actual Loops: 1
    Re= check Cond: "(lower((name)::text) =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::t= ext)"
    Rows Removed by Index Recheck: 0
&n= bsp;   Exact Heap Blocks: 1
    Lossy Heap Blocks:= 0
    Plans: 
      - No= de Type: "Bitmap Index Scan"
        Parent R= elationship: "Outer"
        Parallel Aware: = false
        Index Name: "textlowerindex"
        Startup Cost: 0.00
  &nb= sp;     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) = =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"
  Planning Ti= me: 0.051
  Triggers: 
  Execution Time:= 0.035




expla= in (analyze on, format yaml) select * from test where lower(name)=3Dlower('= f6d7d5be1d0bed1cca11540d3a2667de');

- Plan= : 
    Node Type: "Index Scan"
  &n= bsp; 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<= /div>
    Actual Startup Time: 0.049
    = Actual Total Time: 0.050
    Actual Rows: 1
&= nbsp;   Actual Loops: 1
    Index Cond: "(name =3D= '956d692092f0b9f85f36bf2b2501f3ad'::citext)"
    Rows = Removed by Index Recheck: 0
  Planning Time: 0.051
  Triggers: 
  Execution Time: 0.064

Deepak
------=_Part_2385018_1558798623.1523033474009--