Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l322A-0006AH-Jp for pgsql-bugs@arkaria.postgresql.org; Fri, 22 Jan 2021 19:23:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l3229-0006F7-Fi for pgsql-bugs@arkaria.postgresql.org; Fri, 22 Jan 2021 19:23:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l3229-0006Ez-5A for pgsql-bugs@lists.postgresql.org; Fri, 22 Jan 2021 19:23:49 +0000 Received: from mxh02aws90.mxout.inova.com.br ([3.219.79.34] helo=mxhero01.a.inova.com.br) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l3224-0005nr-EW for pgsql-bugs@lists.postgresql.org; Fri, 22 Jan 2021 19:23:48 +0000 Received: from MXHERO01.A.INOVA.COM.BR (localhost [127.0.0.1]) by mxhero01.a.inova.com.br (Postfix) with ESMTP id C8BA21F14DA for ; Fri, 22 Jan 2021 19:23:41 +0000 (UTC) Received: from mxhero02.a.inova.com.br (unknown [10.0.4.78]) by mxhero01.a.inova.com.br (Postfix) with ESMTP; Fri, 22 Jan 2021 19:23:41 +0000 (UTC) Received: from corpmoutpolicyd01.a.inova.com.br (unknown [10.0.1.88]) by mxhero02.a.inova.com.br (Postfix) with ESMTP; Fri, 22 Jan 2021 19:23:41 +0000 (UTC) Received: from corpmoutpolicyd01.a.inova.com.br (localhost [127.0.0.1]) by corpmoutpolicyd01.a.inova.com.br (Postfix) with ESMTP id B12DD2205F7; Fri, 22 Jan 2021 17:23:37 -0200 (BRST) Received: from corpmout01.a.inova.com.br (ip-10-0-1-125.ec2.internal [10.0.1.125]) by corpmoutpolicyd01.a.inova.com.br (Postfix) with ESMTP id A7479E1696; Fri, 22 Jan 2021 17:23:37 -0200 (BRST) Received: from corpmbox02.a.inova.com.br (ip-10-0-4-158.ec2.internal [10.0.4.158]) by corpmout01.a.inova.com.br (Postfix) with ESMTP; Fri, 22 Jan 2021 16:23:37 -0300 (-03) Date: Fri, 22 Jan 2021 16:23:37 -0300 (BRT) From: luis.roberto@siscobra.com.br To: "Zhang, Hongyan" Cc: Tom Lane , pgsql-bugs Message-ID: <848323852.1456269.1611343417560.JavaMail.zimbra@siscobra.com.br> In-Reply-To: References: <16835-419ddd12f1e74ee5@postgresql.org> <1165678.1611333843@sss.pgh.pa.us> Subject: Re: [EXTERNAL] Re: BUG #16835: btree index does not work for where clause using 'foo%' MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_9ad788b5-14e6-4d30-b3dc-1dd6e8126501" X-Originating-IP: [10.0.1.247] X-Mailer: Zimbra 8.8.15_GA_3980 (ZimbraWebClient - GC88 (Win)/8.8.15_GA_3980) Thread-Topic: BUG #16835: btree index does not work for where clause using 'foo%' Thread-Index: AQHW8N70pIjwHhlEXUCG26+Yne3HmKoznUuAPDxnT+E= x-mxHero-Origin-Ip: /127.0.0.1:48052 X-mxHero-AttachmentBlock: rule=48;result=false List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_9ad788b5-14e6-4d30-b3dc-1dd6e8126501 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit De: "Zhang, Hongyan" Para: "Tom Lane" Cc: "pgsql-bugs" Enviadas: Sexta-feira, 22 de janeiro de 2021 16:07:22 Assunto: Re: [EXTERNAL] Re: BUG #16835: btree index does not work for where clause using 'foo%' Thanks, Tom. Locale is en_US.UTF-8. Is there plan to alter that behavior to allow index in non C locale? According to documentation: " However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries;" So you'll have to create the index with a specific operator class. You can use:. CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops) [1] [1] https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations --=_9ad788b5-14e6-4d30-b3dc-1dd6e8126501 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
De: "Zha= ng, Hongyan" <ZhangH3@aetna.com>
Para: "Tom Lane" <tgl@s= ss.pgh.pa.us>
Cc: "pgsql-bugs" <pgsql-bugs@lists.postgresql= .org>
Enviadas: Sexta-feira, 22 de janeiro de 2021 16:07:22Assunto: Re: [EXTERNAL] Re: BUG #16835: btree index does not work f= or where clause using 'foo%'

Thanks, Tom.

Locale is en_US.UTF-8.

 

Is there plan to alter that behavior to allow index = in non C locale?

 


According to doc= umentation:  "However, if your= database does not use the C locale you will need to create the index with = a special operator class to support indexing of pattern-matching queries;"<= /span>

So you'll have to create the index with a specific operator class.<= /div>

You can use:.

CREATE INDEX tbl_col_text_pa= ttern_ops_idx ON tbl(col text_pattern_ops) [1]


[1] https://stackoverflow.com/questions/1566717/postgresql-like-q= uery-performance-variations
--=_9ad788b5-14e6-4d30-b3dc-1dd6e8126501--