Thread: Proposal for col LIKE $1 with generic Plan
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
'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;
The second step is we need to define greater string for $1 just like
make_greater_string. Looks we have to know the exact value to make the greater
string, so we can define a new FuncExpr like this:
Index Cond: ((md5 >= $1::text) AND (md5 < make_greater_string_fn($1)).
This may not be able to fix handle the current make_greater_string return NULL
case. so we may define another FuncExpr like text_less_than_or_null
Index Cond: ((md5 >= $1::text) AND (text_less_than_or_null(md5, make_greater_string_fn($1)))
Is this a right thing to do and a right method?
'abc' ~^ 'ab` -> true
'abc' ~^ 'ab%' -> false
so the above case can be written as:
PREPARE s AS SELECT * FROM t WHERE a ~^ $1;
The second step is we need to define greater string for $1 just like
make_greater_string. Looks we have to know the exact value to make the greater
string, so we can define a new FuncExpr like this:
Index Cond: ((md5 >= $1::text) AND (md5 < make_greater_string_fn($1)).
This may not be able to fix handle the current make_greater_string return NULL
case. so we may define another FuncExpr like text_less_than_or_null
Index Cond: ((md5 >= $1::text) AND (text_less_than_or_null(md5, make_greater_string_fn($1)))
Is this a right thing to do and a right method?
Thanks
-- Best Regards
Andy Fan (https://www.aliyun.com/)
On Thu, Mar 25, 2021 at 10:15 AM Andy Fan <zhihui.fan1213@gmail.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 thinkwe need to define an new operator like text(a) ~^ text(b), which means ais 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!
Best Regards
Andy Fan (https://www.aliyun.com/)