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 1f5Gi8-0000Eh-5B for pgsql-performance@arkaria.postgresql.org; Sun, 08 Apr 2018 20:14:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f5Gi6-0008DN-8z for pgsql-performance@arkaria.postgresql.org; Sun, 08 Apr 2018 20:14:46 +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 1f5Gi5-0008DE-Nn for pgsql-performance@lists.postgresql.org; Sun, 08 Apr 2018 20:14:46 +0000 Received: from sonic310-21.consmr.mail.gq1.yahoo.com ([98.137.69.147]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f5Ghv-0005MC-Vn for pgsql-performance@lists.postgresql.org; Sun, 08 Apr 2018 20:14:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1523218471; bh=3ODtPSRbwhS89OT0DlfDgflA1oCIdPsmi0sGOxFtcgk=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=pQ9wFul7kNj8d11sEodNACR1yssci5wrlzIW/fhaS7kCAPeUGhCsyD90KbgUEXUBQLgKCxGenAnYOpxDlEzpA68d+5UJs5M+QnSNyGt4MODFIucB7fkYGAG+boT3DBIheKQefxybMKg+/zsrKSXjE50P9tzHNa2nSm6keLdJXYpe/NwX1SIQuEuJE/OIdLy4r5IpintQWrE9cVUitecAB4gswRX0+IXJB/B2ga9Ek2TpZx8QIwMpPlBEhdh6iplYGg8sJLxlQghgdfaQ3rH9oQWwrvqtWJh+M7gcxG3udjMuodwLGbI56d64WB4lCvrMmAjv6/YWgq2V8bVmLpl1Bw== X-YMail-OSG: Ini7ROkVM1lj9kPtyKpm8vKb6pTZ9dp.4uCoNsSHwU5vS5U0OhUpMGJ5qCQ6mDn IYHhcy3qCpl1Ugk8j2Gog3fKKZBBDI3oeOzgFlaARlFAmNd_kQtpZ0TB7.gbYvIZIV9YtA_axvYO 9GzZDnGdRokN9XB.fqR2QVj8G0sL0p2Qoea7_ztewDBZaIlIt2XwGVByUBuOHtzIqo0r2BUYCHz_ UDiE2KZyFnPG544cC4SuikWOXPVvbl73ofzHSvsKTZmah2m5A7FSS090xP_3XAFURzsV2YPei4gQ Dqwi._EY5fcYy_EbqcFZBMMwmeB0B0pj7cYWcx2ABclaYFK7tl8nMvOUl_1QIW0xt98a96QNk7Cs OwNEjlOuY.sZyAzh6UszsCeVZo_nANVpJuHpMxgGwoj8_UYJdub3jFs0h3lamy65mY9411E6K0nO Kp5.gZ.4Y4jrz5RSatbZJiP2ucon67qZ_ftbK98JxkLwO0Ethfz8iD9Gd Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.gq1.yahoo.com with HTTP; Sun, 8 Apr 2018 20:14:31 +0000 Date: Sun, 8 Apr 2018 20:14:29 +0000 (UTC) From: Deepak Somaiya To: Nandakumar M Cc: "pgsql-performance@lists.postgresql.org" , "david@kineticode.com" Message-ID: <1522749620.304966.1523218469730@mail.yahoo.com> In-Reply-To: References: <1991635447.2385019.1523033474018.ref@mail.yahoo.com> <1991635447.2385019.1523033474018@mail.yahoo.com> Subject: Re: citext performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_304965_1873394554.1523218469728" X-Mailer: WebService/1.1.11745 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: 17406 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_304965_1873394554.1523218469728 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable It is using index here , it is just that performance i.e query that use fu= nctional index (one with lower) is performing better then index created on = citext column. Deepak On Sunday, April 8, 2018, 3:13:26 AM PDT, Nandakumar M wrote: =20 =20 Hi, I have also faced the same problem with citext extension. It does not use index when thereby making it almost unusable. The problem has to do with how collation is handled from what I have read in old threads in postgres mailing list (please refer https://dba.stackexchange.com/questions/105244/index-on-column-with-data-ty= pe-citext-not-used/105250#105250 ). Regards, Nanda On Fri, Apr 6, 2018 at 10:21 PM, Deepak Somaiya wrote: > > Folks, >=C2=A0 I read following (PostgreSQL: Documentation: 9.6: citext) and it do= es not hold true in my testing.. i.e citext is not performing better than l= ower.Am I missing something? help is appreciated. > > PostgreSQL: Documentation: 9.6: citext > > > > > "citext is not as efficient as text because the operator functions and th= e B-tree comparison functions must make copies of the data and convert it t= o 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)=3D= lower('f6d7d5be1d0bed1cca11540d3a2667de'); > - Plan: >=C2=A0 =C2=A0 Node Type: "Seq Scan" >=C2=A0 =C2=A0 Parallel Aware: 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 Rows: 1 >=C2=A0 =C2=A0 Actual Loops: 1 >=C2=A0 =C2=A0 Filter: "(lower((name)::text) =3D 'f6d7d5be1d0bed1cca11540d3= a2667de'::text)" >=C2=A0 =C2=A0 Rows Removed by Filter: 999999 >=C2=A0 Planning Time: 0.045 >=C2=A0 Triggers: >=C2=A0 Execution Time: 680.213 > > > explain (analyze on, format yaml) select * from testci where name=3D'956d= 692092f0b9f85f36bf2b2501f3ad'; > - Plan: >=C2=A0 =C2=A0 Node Type: "Seq Scan" >=C2=A0 =C2=A0 Parallel Aware: 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 Rows: 1 >=C2=A0 =C2=A0 Actual Loops: 1 >=C2=A0 =C2=A0 Filter: "(name =3D '956d692092f0b9f85f36bf2b2501f3ad'::citex= t)" >=C2=A0 =C2=A0 Rows Removed by Filter: 999999 >=C2=A0 Planning Time: 0.029 >=C2=A0 Triggers: >=C2=A0 Execution Time: 1184.496 > > > > You can see sequential searches with lower working twice as fast as citex= t. > > Now I added index on citext and equivalent functional index (lower) on te= xt. > > > CREATE INDEX textlowerindex ON test (lower(name)); > create index textindex on test(name); > > > Index creation took longer with citext v/s creating lower functional inde= x. > > > Now here comes execution with indexes > > explain (analyze on, format yaml) select * from test where lower(name)=3D= lower('f6d7d5be1d0bed1cca11540d3a2667de'); > > - Plan: >=C2=A0 =C2=A0 Node Type: "Bitmap Heap Scan" >=C2=A0 =C2=A0 Parallel 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 Actual Rows: 1 >=C2=A0 =C2=A0 Actual Loops: 1 >=C2=A0 =C2=A0 Recheck Cond: "(lower((name)::text) =3D 'f6d7d5be1d0bed1cca1= 1540d3a2667de'::text)" >=C2=A0 =C2=A0 Rows 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 - Node 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 'f6d7d5b= e1d0bed1cca11540d3a2667de'::text)" >=C2=A0 Planning Time: 0.051 >=C2=A0 Triggers: >=C2=A0 Execution Time: 0.035 > > > > > explain (analyze on, format yaml) select * from test where lower(name)=3D= lower('f6d7d5be1d0bed1cca11540d3a2667de'); > > - Plan: >=C2=A0 =C2=A0 Node Type: "Index Scan" >=C2=A0 =C2=A0 Parallel Aware: 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: "testci" >=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 '956d692092f0b9f85f36bf2b2501f3ad'::c= itext)" >=C2=A0 =C2=A0 Rows Removed by Index Recheck: 0 >=C2=A0 Planning Time: 0.051 >=C2=A0 Triggers: >=C2=A0 Execution Time: 0.064 > > > Deepak =20 ------=_Part_304965_1873394554.1523218469728 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
It is using index here , it is just that performance i.e q= uery that use functional index (one with lower) is performing better then i= ndex created on citext column.

Deepak
=20
=20
On Sunday, April 8, 2018, 3:13:26 AM PDT, Nandakuma= r M <m.nanda92@gmail.com> wrote:


Hi,

I have also faced the same problem with citext extension. It does= not
use index when thereby making it almost unusable. Th= e problem has to
do with how collation is handled from wh= at I have read in old threads
in postgres mailing list (p= lease refer
https://dba.stackexchange= .com/questions/105244/index-on-column-with-data-type-citext-not-used/105250= #105250
).

Regar= ds,
Nanda

On Fri, Apr 6, 2= 018 at 10:21 PM, Deepak Somaiya <deepsom@yahoo.com> w= rote:
>
> 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 t= han lower.Am I missing something? help is appreciated.
&g= t;
> PostgreSQL: Documentation: 9.6: citext
>
>
>
>
> "citext is not as efficient as text because= the operator functions and the B-tree comparison functions must make copie= s 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 tab= le 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 gen= erate_series(1000001,2000000), (md5(random()::text));
>= ;
> INSERT INTO testci(id, name)
>= ; SELECT generate_series(1,1000000), (md5(random()::text));
>
>
> Now, I have done sequ= ential search
>
> explain (analyz= e on, format yaml) select * from test where lower(name)=3Dlower('f6d7d5be1d= 0bed1cca11540d3a2667de');
> - Plan:
= >    Node Type: "Seq Scan"
>   = ; Parallel Aware: false
>    Relation Name= : "test"
>    Alias: "test"
>    Startup Cost: 0.00
>   = ; Total Cost: 23334.00
>    Plan Rows: 500= 0
>    Plan Width: 37
>= ;    Actual Startup Time: 0.016
>  &nb= sp; Actual Total Time: 680.199
>    Actual= Rows: 1
>    Actual Loops: 1
>    Filter: "(lower((name)::text) =3D 'f6d7d5be1d0bed1c= ca11540d3a2667de'::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= =3D'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 =3D '956d692092f0b9f8= 5f36bf2b2501f3ad'::citext)"
>    Rows Remov= ed by Filter: 999999
>  Planning Time: 0.029
>  Triggers:
>  Executio= n Time: 1184.496
>
>
>
> You can see sequential searches with lowe= r working twice as fast as citext.
>
> Now I added index on citext and equivalent functional index (lower) o= n 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) sel= ect * from test where lower(name)=3Dlower('f6d7d5be1d0bed1cca11540d3a2667de= ');
>
> - Plan:
>    Node Type: "Bitmap Heap Scan"
>&nb= sp;   Parallel Aware: false
>    Rela= tion Name: "test"
>    Alias: "test"
>    Startup Cost: 187.18
>&= nbsp;   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) =3D 'f6= d7d5be1d0bed1cca11540d3a2667de'::text)"
>   = Rows Removed by Index Recheck: 0
>    Exa= ct Heap Blocks: 1
>    Lossy Heap Blocks: 0=
>    Plans:
>  &= nbsp;   - Node Type: "Bitmap Index Scan"
> =       Parent Relationship: "Outer"
>&= nbsp;       Parallel Aware: false
>&nb= sp;       Index Name: "textlowerindex"
&g= t;        Startup Cost: 0.00
>&nb= sp;       Total Cost: 185.93
>  &= nbsp;     Plan Rows: 5000
>    &n= bsp;   Plan Width: 0
>       = ; Actual Startup Time: 0.016
>      &n= bsp; Actual Total Time: 0.016
>      &= nbsp; Actual Rows: 1
>        Ac= tual Loops: 1
>        Index Cond= : "(lower((name)::text) =3D 'f6d7d5be1d0bed1cca11540d3a2667de'::text)"
>  Planning Time: 0.051
> = Triggers:
>  Execution Time: 0.035
>
>
>
>
> explain (analyze on, format yaml) select * fro= m test where lower(name)=3Dlower('f6d7d5be1d0bed1cca11540d3a2667de');
>
> - Plan:
>&nbs= p;   Node Type: "Index Scan"
>    Par= allel Aware: false
>    Scan Direction: "Fo= rward"
>    Index Name: "citextindex"
>    Relation Name: "testci"
&= gt;    Alias: "testci"
>    Star= tup Cost: 0.42
>    Total Cost: 8.44
>    Plan Rows: 1
>  &n= bsp; Plan Width: 37
>    Actual Startup Ti= me: 0.049
>    Actual Total Time: 0.050
>    Actual Rows: 1
>&nbs= p;   Actual Loops: 1
>    Index Cond:= "(name =3D '956d692092f0b9f85f36bf2b2501f3ad'::citext)"
= >    Rows Removed by Index Recheck: 0
>&= nbsp; Planning Time: 0.051
>  Triggers:
>  Execution Time: 0.064
>
>
> Deepak
------=_Part_304965_1873394554.1523218469728--