babelfish-for-postgresql / babelfish_extensions / 14970420966

77%

Build Type

push

github

Committed by web-flow

Commit Message

Use Index Scan for EXACT and PREFIX pattern matches for LIKE  (#3582)

We have supported LIKE from TSQL side for case insensitive collation by transforming LIKE to ILIKE (this is a PG operator
which sorts strings case insensitively based on active locale). We have also introduced optimisation in place for the case
when the rightop of the LIKE operator is a Const node and has prefix. Eg:

-- CASE 1
SELECT COL FROM TAB WHERE COL LIKE 'ab'

-- CASE 2
SELECT COL FROM TAB WHERE COL LIKE 'a%'

This is the optimisation that we follow:

expression LIKE pattern

will become if pattern has exact match

expression = pattern

will become if pattern has prefix match

expression ILIKE pattern COLLATE cs_as AND
expression BETWEEN patternConstPrefix AND patternConstPrefix || E'\uFFFF 

E’\uFFFF is the highest sort key. We use the BETWEEN operator as in PG we can NOT use index scan for LIKE/ILIKE
operator. Whereas introducing BETWEEN will enable us to do so. And the CS_AS collation is used to store the locale info
for ILIKE.

For supporting case insensitive accent insensitive collations like latin1_general_ci_ai , we transform the node firstly by
adding a FuncExpr (remove_accents_internal) which removes the accent and then we apply the above logic.

Currently we have observed that we do not comply with the transformation above for CS collations. Rather than
COLLATE CS_AS, we observe COLLATE “default” in query plan. Consider the following query:

-- query (column a is collated with latin1_genetal_ci_as)
select a from varchar_tst where a collate chinese_prc_cs_as LIKE 'ab%'

-- query plan
Filter: (((a)::text ~~ 'ab%'::text COLLATE "default") 
This commit handles case sensitive collation in babelfish side as well with the above mentioned optimisation for EXACT and PREFIX pattern match.

We have the optimisation for EXACT and PREFIX pattern match so that =, > and <= operators can use Index when the left
operand has an Index on it. Till now babelfish was unable to use Inde... (continued)

Run Details

49 of 50 new or added lines in 3 files covered. (98.0%)

3 existing lines in 1 file now uncovered.

47998 of 63848 relevant lines covered (75.18%)

299753.47 hits per line