Nick Dimiduk created PHOENIX-3325: ------------------------------------- Summary: optimize lhs = ANY(...) as lhs in (...) Key: PHOENIX-3325 URL: https://issues.apache.org/jira/browse/PHOENIX-3325 Project: Phoenix Issue Type: Improvement Affects Versions: 4.7.0 Reporter: Nick Dimiduk
Phoenix will efficiently optimize a {{WHERE x IN (...)}} clause, when x is part of the primary key, into a skip-scan or point lookup, based on the column's rowkey position. The same could be done for a {{WHERE x = ANY(...)}}. This would be especially convenient for clients using prepared statements where the number of element compared is not known ahead of time. They may prepare the query once using {{ANY}} and bind an array, rather than re-building it each invocation with a concatenation of {{?}}'s based on the member size. {noformat} 0: jdbc:phoenix:localhost> explain select * from test1 where a = any(ARRAY[1,3]); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TEST1 | | SERVER FILTER BY org.apache.phoenix.expression.function.ArrayAnyComparisonExpression [children=[ARRAY[1,3], A = org.apache.phoenix.expression.function.ArrayElemRefExpression [children=[ARRAY[1,3], | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows selected (0.008 seconds) 0: jdbc:phoenix:localhost> explain select * from test1 where a in (1,3); +------------------------------------------------------------------+ | PLAN | +------------------------------------------------------------------+ | CLIENT 1-CHUNK PARALLEL 1-WAY POINT LOOKUP ON 2 KEYS OVER TEST1 | +------------------------------------------------------------------+ 1 row selected (0.039 seconds) {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)