:wave: Hi team, just have a question regarding `RE...
# troubleshooting
s
👋 Hi team, just have a question regarding
REGEX_LIKE
used with
JSON_MATCH
. When used with exact matches, are there any performance degradation moving from just using
JSON_MATCH
to using with
REGEX_LIKE
?
x
It will be slow with Regex_like. No index for that, you can try json index to speed up. Or text index https://docs.pinot.apache.org/basics/indexing/text-search-support
b
Hi Soon. Were you asking about switching from e.g.
Copy code
SELECT * 
FROM  tab
JSON_MATCH(json, '"$.id" =''ABC'' ')
to
Copy code
SELECT *
FROM tab
WHERE JSON_MATCH(json, 'REGEXP_LIKE("$.id", ''AB.*'')')
? If so, the former should be faster than the latter, but actual difference depends on number of values for a given key. In json index, REGEXP_LIKE tries to match given pattern against all values for given key. If the number of values is big and/or matching is slow, it might show up in response time.
s
Thank you for the response. I was thinking switching from
Copy code
SELECT * 
FROM  tab
JSON_MATCH(json, '"$.id" =''ABC'' ')
to
Copy code
SELECT *
FROM tab
WHERE JSON_MATCH(json, 'REGEXP_LIKE("$.id", ''ABC'')')
I'm trying to see if I can make regexp_like function as a default function for all text matches in json index and wanted to check if there would be any performance degradation for exact matches. Would the following switch cause any latency increase? Or Is there any shortcircuit in exact matches when used with regex_like function?
b
It depends on number of values for given key - "$.id" . If it's large then trying to match against pattern might slow down the query. In comparison, for equality filter such as
'"$.id" =''ABC'' '
, pinot simply looks up ".id=ABC" entry in internal dictionary.