pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16835: btree index does not work for where clause using 'foo%'
5+ messages / 4 participants
[nested] [flat]

* BUG #16835: btree index does not work for where clause using 'foo%'
@ 2021-01-22 15:56  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: PG Bug reporting form @ 2021-01-22 15:56 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: zhangh3@aetna.com

The following bug has been logged on the website:

Bug reference:      16835
Logged by:          Hongyan (Heather) Zhang
Email address:      zhangh3@aetna.com
PostgreSQL version: 11.9
Operating system:   aurora postgres 11
Description:        

https://www.postgresql.org/docs/11/indexes-types.html, according to this
page, btree index is supposed to work for where clause col like 'foo%', but
does not work for me. we are on aurora postgres 11, I also tested on
postgres 11, it does not work either.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16835: btree index does not work for where clause using 'foo%'
@ 2021-01-22 16:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2021-01-22 16:44 UTC (permalink / raw)
  To: zhangh3@aetna.com; +Cc: pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> writes:
> https://www.postgresql.org/docs/11/indexes-types.html, according to this
> page, btree index is supposed to work for where clause col like 'foo%', but
> does not work for me. we are on aurora postgres 11, I also tested on
> postgres 11, it does not work either.

As the manual explains, that will not work by default unless your
database locale is C (which is an uncommon default nowadays).

It's also possible that Aurora has changed something that keeps
it from working, but I'd investigate the locale angle first.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [EXTERNAL] Re: BUG #16835: btree index does not work for where clause using 'foo%'
@ 2021-01-22 19:07  Zhang, Hongyan <ZhangH3@aetna.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Zhang, Hongyan @ 2021-01-22 19:07 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Thanks, Tom.
Locale is en_US.UTF-8.

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

From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Friday, January 22, 2021 at 10:52 AM
To: "Zhang, Hongyan" <ZhangH3@aetna.com>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: [EXTERNAL] Re: BUG #16835: btree index does not work for where clause using 'foo%'

**** External Email - Use Caution ****

PG Bug reporting form <noreply@postgresql.org> writes:
> https://www.postgresql.org/docs/11/indexes-types.html<https://www.postgresql.org/docs/11/indexes-...;, according to this
> page, btree index is supposed to work for where clause col like 'foo%', but
> does not work for me. we are on aurora postgres 11, I also tested on
> postgres 11, it does not work either.

As the manual explains, that will not work by default unless your
database locale is C (which is an uncommon default nowadays).

It's also possible that Aurora has changed something that keeps
it from working, but I'd investigate the locale angle first.

regards, tom lane

 NOTICE TO RECIPIENT OF INFORMATION:
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately.  

This e-mail may also contain protected health information (PHI) with information about sensitive medical conditions, including, but not limited to, treatment for substance use disorders, behavioral health, HIV/AIDS, or pregnancy. This type of information may be protected by various federal and/or state laws which prohibit any further disclosure without the express written consent of the person to whom it pertains or as otherwise permitted by law. Any unauthorized further disclosure may be considered a violation of federal and/or state law. A general authorization for the release of medical or other information may NOT be sufficient consent for release of this type of information.

Thank you. Aetna


^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [EXTERNAL] Re: BUG #16835: btree index does not work for where clause using 'foo%'
@ 2021-01-22 19:23  luis.roberto@siscobra.com.br
  parent: Zhang, Hongyan <ZhangH3@aetna.com>
  0 siblings, 1 reply; 5+ messages in thread

From: luis.roberto@siscobra.com.br @ 2021-01-22 19:23 UTC (permalink / raw)
  To: Zhang, Hongyan <ZhangH3@aetna.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

De: "Zhang, Hongyan" <ZhangH3@aetna.com> 
Para: "Tom Lane" <tgl@sss.pgh.pa.us> 
Cc: "pgsql-bugs" <pgsql-bugs@lists.postgresql.org> 
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 

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [EXTERNAL] Re: BUG #16835: btree index does not work for where clause using 'foo%'
@ 2021-01-22 19:45  Zhang, Hongyan <ZhangH3@aetna.com>
  parent: luis.roberto@siscobra.com.br
  0 siblings, 0 replies; 5+ messages in thread

From: Zhang, Hongyan @ 2021-01-22 19:45 UTC (permalink / raw)
  To: luis.roberto@siscobra.com.br <luis.roberto@siscobra.com.br>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

Thanks, Luis

From: "luis.roberto@siscobra.com.br" <luis.roberto@siscobra.com.br>
Date: Friday, January 22, 2021 at 1:28 PM
To: "Zhang, Hongyan" <ZhangH3@aetna.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: [EXTERNAL] Re: BUG #16835: btree index does not work for where clause using 'foo%'

**** External Email - Use Caution ****
________________________________
De: "Zhang, Hongyan" <ZhangH3@aetna.com>
Para: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "pgsql-bugs" <pgsql-bugs@lists.postgresql.org>
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<https://...;

 NOTICE TO RECIPIENT OF INFORMATION:
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately.  

This e-mail may also contain protected health information (PHI) with information about sensitive medical conditions, including, but not limited to, treatment for substance use disorders, behavioral health, HIV/AIDS, or pregnancy. This type of information may be protected by various federal and/or state laws which prohibit any further disclosure without the express written consent of the person to whom it pertains or as otherwise permitted by law. Any unauthorized further disclosure may be considered a violation of federal and/or state law. A general authorization for the release of medical or other information may NOT be sufficient consent for release of this type of information.

Thank you. Aetna


^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2021-01-22 19:45 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-22 15:56 BUG #16835: btree index does not work for where clause using 'foo%' PG Bug reporting form <noreply@postgresql.org>
2021-01-22 16:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-22 19:07   ` Zhang, Hongyan <ZhangH3@aetna.com>
2021-01-22 19:23     ` luis.roberto@siscobra.com.br
2021-01-22 19:45       ` Zhang, Hongyan <ZhangH3@aetna.com>

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