Re: Allow ILIKE forward matching to use btree index

2025-03-31 Thread Yugo NAGATA
On Thu, 16 Jan 2025 09:41:35 -0800 Jeff Davis wrote: > On Thu, 2025-01-16 at 14:53 +0900, Yugo NAGATA wrote: > > Instead of generating complete patterns considering every case- > > varying characters, > > two clauses considering only the first case-varying character are > > generated. > > Did yo

Re: Allow ILIKE forward matching to use btree index

2025-01-16 Thread Jeff Davis
On Thu, 2025-01-16 at 14:53 +0900, Yugo NAGATA wrote: > Instead of generating complete patterns considering every case- > varying characters, > two clauses considering only the first case-varying character are > generated. Did you consider other approaches that integrate more deeply into the index

Re: Allow ILIKE forward matching to use btree index

2025-01-15 Thread Yugo NAGATA
On Wed, 15 Jan 2025 14:40:19 -0800 Jeff Davis wrote: > My apologies, I sent the previous email prematurely. Let me try again: > > On Wed, 2025-01-15 at 14:34 -0800, Jeff Davis wrote: > > On Wed, 2025-01-15 at 01:40 +0900, Yugo NAGATA wrote: > > > > > For example, "t ~~ '123foo%'" is converted to

Re: Allow ILIKE forward matching to use btree index

2025-01-15 Thread Jeff Davis
My apologies, I sent the previous email prematurely. Let me try again: On Wed, 2025-01-15 at 14:34 -0800, Jeff Davis wrote: > On Wed, 2025-01-15 at 01:40 +0900, Yugo NAGATA wrote: > > > > For example, "t ~~ '123foo%'" is converted to "(t >= '123foo' > > > > AND > > > > t < '123fop')" > > > > and

Re: Allow ILIKE forward matching to use btree index

2025-01-15 Thread Jeff Davis
On Wed, 2025-01-15 at 01:40 +0900, Yugo NAGATA wrote: > > > For example, "t ~~ '123foo%'" is converted to "(t >= '123foo' AND > > > t < '123fop')" > > > and index scan can be used for this condition. On the other hand, > > > "t ~~* '123foo'" > > > cannot be converted and sequential scan is used.

Re: Allow ILIKE forward matching to use btree index

2025-01-14 Thread Yugo NAGATA
On Tue, 24 Dec 2024 16:04:42 +0900 Yugo Nagata wrote: > On Fri, 20 Dec 2024 03:22:26 +0900 > Yugo Nagata wrote: > > > Hi, > > > > Currently, btree indexes cannot used for ILIKE (~~*) operator if the pattern > > has case-varying characters although LIKE (~~) expression can be converted > > to i

Re: Allow ILIKE forward matching to use btree index

2024-12-23 Thread Yugo Nagata
On Fri, 20 Dec 2024 03:22:26 +0900 Yugo Nagata wrote: > Hi, > > Currently, btree indexes cannot used for ILIKE (~~*) operator if the pattern > has case-varying characters although LIKE (~~) expression can be converted > to indexable clauses by the planner support function (if the collation > is