Re: Proposal for col LIKE $1 with generic Plan

On Thu, Mar 25, 2021 at 10:15 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

> Thanks to the get_index_clause_from_support, we can use index for WHERE a
> like
> 'abc%' case. However this currently only works on custom plan. Think about
> the
> case where the planning takes lots of time, custom plan will not give a
> good
> result. so I want to see if we should support this for a generic plan as
> well.
>
> The first step of this is we need to find an operator to present prefix is
> just
> literal. which means '%' is just '%', not match any characters. After
> trying
> 'like' and '~' operator, I find none of them can be used. for example:
>
> PREPARE s AS SELECT * FROM t WHERE a LIKE ('^' || $1);
> EXECUTE s('%abc');
>
> '%' is still a special character to match any characters. So '~' is. So
> I think
> we need to define an new operator like text(a) ~^ text(b), which means a
> is prefixed with b literally. For example:
>
> 'abc' ~^ 'ab` -> true
> 'abc' ~^ 'ab%' -> false
>
> so the above case can be written as:
>
> PREPARE s AS SELECT * FROM t WHERE a ~^ $1;
>
>
During the PoC coding, I found we already have ^@ operator for this [1], but
we don't implement that for BTree index so far. So I will try gist index
for my
current user case and come back to this thread later. Thanks!

[1]
https://www.postgresql.org/message-id/20180416155036.36070396%40wp.localdomain

--
Best Regards
Andy Fan (https://www.aliyun.com/)