http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> What is the most efficient array fu...
# questions-and-troubleshooting
m
@Rocky What is the most efficient array functions to perform single and multi value lookups?
r
For single and multi-value lookups in StarRocks, the most efficient functions depend on whether you need to match one, any, or all values. 1. Single Value Lookup * 
array_contains(arr, element)
: This is the most direct and optimized function to check if a specific value exists in an array. It returns 
1
(true) or 
0
(false).
Copy code
sql
    SELECT array_contains([1, 2, 3], 2); -- Returns 1
2. Multi-Value Lookup ("Contains Any") If you want to check if an array contains at least one element from another set of values, use: * 
arrays_overlap(arr1, arr2)
: This is more efficient than performing multiple 
OR
conditions with 
array_contains
. It returns 
1
if the intersection of the two arrays is not empty.
Copy code
sql
    SELECT arrays_overlap([1, 2, 3], [3, 4, 5]); -- Returns 1 (because 3 matches)
3. Multi-Value Lookup ("Contains All") If you need to verify that an array contains every element from another set: * 
array_contains_all(arr1, arr2)
: Checks if 
arr2
is a complete subset of 
arr1
.
Copy code
sql
    SELECT array_contains_all([1, 2, 3, 4], [1, 2]); -- Returns 1
4. Advanced/Conditional Lookups If your lookup logic is more complex (e.g., checking for values greater than X or matching a pattern): * 
any_match(lambda, arr)
 (v3.0.6+): Returns true if any element matches a lambda predicate.
Copy code
sql
    SELECT any_match(x -> x > 10, [5, 8, 12]); -- Returns 1
* 
array_filter(lambda, arr)
: If you need to actually retrieve the matching values rather than just a boolean check. Performance Tip StarRocks array functions are vectorized. For the best performance: • Prefer 
arrays_overlap
over 
array_intersect(...).size() > 0
. • Avoid using 
array_join
or string-based lookups (like 
LIKE
or 
REGEXP
) on arrays, as these bypass the performance benefits of the underlying nested data structure. of the native 
ARRAY
type. References • sql-reference/sql-functions/array-functions/arrays_overlap.md • sql-reference/sql-functions/array-functions/array_intersect.md • sql-reference/sql-functions/array-functions/array_contains_seq.md • sql-reference/sql-functions/array-functions/array_contains_all.md • sql-reference/data-types/semi_structured/Array.md • sql-reference/sql-functions/bitmap-functions/intersect_count.md
m
@Rocky
Copy code
SELECT SUM(num_requests) AS total_requests,
         SUM(CASE
    WHEN audio_feed
    BETWEEN 1
        AND 7 THEN
    num_requests
    ELSE 0 END) AS valid_feed_requests, ROUND(100.0 * SUM(CASE
    WHEN audio_feed
    BETWEEN 1
        AND 7 THEN
    num_requests
    ELSE 0 END) / SUM(num_requests), 2) AS pct_valid_feed
FROM requests.requests
WHERE received >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
        AND ( array_contains(deal_ids, 'ac3efb4c-3a2e-49b5-8c8b-0c4cec176786')
        OR array_contains(deal_ids, '2c5c5939-8045-4316-a37f-27ce6db7939e')
        OR array_contains(deal_ids, '187d2cf7-fa64-4ad3-acef-2c6094766b34')
        OR array_contains(deal_ids, '53ab853e-ee4b-4826-88a6-1bb3ba1bee29')
        OR array_contains(deal_ids, 'f4356310-664b-4012-a817-265c1effb1e7')
        OR array_contains(deal_ids, 'c7d014c4-7dd5-4115-8d61-d51c69bca435')
        OR array_contains(deal_ids, 'e76e95d0-ff45-4e2e-958d-95188a9bac59')
        OR array_contains(deal_ids, 'f83ee5ea-59f0-4e27-9a94-ae3178234412')
        OR array_contains(deal_ids, '62209bb6-deab-4975-ad2b-634a1939a5cb')
        OR array_contains(deal_ids, 'c1fd19c1-372e-43b1-8c50-98a7bc08e7f3')
        OR array_contains(deal_ids, 'e33b54eb-1213-4425-bb78-c96b5288f78b')
        OR array_contains(deal_ids, 'f6706ca5-1620-4559-b43f-e1b235d4a93e')
        OR array_contains(deal_ids, '445d9a35-f5f7-4fc8-a1f3-9a5f93c8c209')
        OR array_contains(deal_ids, '49d7afa1-747c-40ea-a9e3-21afffefaa07')
        OR array_contains(deal_ids, '37cf9c5f-9ee9-4319-a419-5671e860878b')
        OR array_contains(deal_ids, 'c25a577d-8078-4c9e-9aa7-0537a78c7308')
        OR array_contains(deal_ids, 'db825c89-0b27-4329-b2f1-3ac335fbaf53')
        OR array_contains(deal_ids, '573fa7a1-ab93-4c83-8e5e-297f774c8735')
        OR array_contains(deal_ids, '1bde2fe3-bc15-4545-90e6-cbde04926933')
        OR array_contains(deal_ids, '0e0aa1c7-b645-4c51-88a2-9f55a7e1f8b1')
        OR array_contains(deal_ids, 'bde2ea06-5364-4b1d-b8af-32e363e1f724') );
Does using arrays_overlap here improve query speed in any significant manner?
r
Yes, using 
arrays_overlap
 will significantly improve query performance compared to a long chain of 
OR array_contains
conditions. Why it improves speed: 1. Reduced Function Overhead: In your original query, StarRocks has to evaluate the 
array_contains
function up to 21 times per row. With 
arrays_overlap
, it is a single function call. 2. Expression Tree Complexity: A large number of 
OR
conditions creates a complex expression tree for the optimizer and execution engine to traverse. Consolidating them into one function call simplifies the execution plan. 3. Vectorized Optimization: StarRocks is a vectorized engine. When you pass a constant array to 
arrays_overlap
, the engine can build an internal hash set for the lookup values once and then efficiently check each row against that set in a single pass. 4. Index Friendliness: If you use Inverted Indexes (available from v3.3+), 
arrays_overlap
is more easily optimized by the storage engine to prune data at the scan level than multiple separate 
OR
conditions. Rewritten Query:
Copy code
sql
SELECT SUM(num_requests) AS total_requests,
       SUM(CASE 
             WHEN audio_feed BETWEEN 1 AND 7 THEN num_requests 
             ELSE 0 
           END) AS valid_feed_requests, 
       ROUND(100.0 * SUM(CASE 
                           WHEN audio_feed BETWEEN 1 AND 7 THEN num_requests 
                           ELSE 0 
                         END) / SUM(num_requests), 2) AS pct_valid_feed
FROM requests.requests
WHERE received >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND arrays_overlap(deal_ids, [
      'ac3efb4c-3a2e-49b5-8c8b-0c4cec176786',
      '2c5c5939-8045-4316-a37f-27ce6db7939e',
      '187d2cf7-fa64-4ad3-acef-2c6094766b34',
      '53ab853e-ee4b-4826-88a6-1bb3ba1bee29',
      'f4356310-664b-4012-a817-265c1effb1e7',
      'c7d014c4-7dd5-4115-8d61-d51c69bca435',
      'e76e95d0-ff45-4e2e-958d-95188a9bac59',
      'f83ee5ea-59f0-4e27-9a94-ae3178234412',
      '62209bb6-deab-4975-ad2b-634a1939a5cb',
      'c1fd19c1-372e-43b1-8c50-98a7bc08e7f3',
      'e33b54eb-1213-4425-bb78-c96b5288f78b',
      'f6706ca5-1620-4559-b43f-e1b235d4a93e',
      '445d9a35-f5f7-4fc8-a1f3-9a5f93c8c209',
      '49d7afa1-747c-40ea-a9e3-21afffefaa07',
      '37cf9c5f-9ee9-4319-a419-5671e860878b',
      'c25a577d-8078-4c9e-9aa7-0537a78c7308',
      'db825c89-0b27-4329-b2f1-3ac335fbaf53',
      '573fa7a1-ab93-4c83-8e5e-297f774c8735',
      '1bde2fe3-bc15-4545-90e6-cbde04926933',
      '0e0aa1c7-b645-4c51-88a2-9f55a7e1f8b1',
      'bde2ea06-5364-4b1d-b8af-32e363e1f724'
  ]);
Pro-tip for further performance: If this table is very large and this filter is common, consider adding an Inverted Index to the 
deal_ids
column:
Copy code
sql
ALTER TABLE requests.requests ADD INDEX idx_deal_ids (deal_ids) USING INVERTED;
This allows StarRocks to skip reading rows that don't contain any of the specified UUIDs entirely. References • sql-reference/sql-functions/array-functions/arrays_overlap.md • sql-reference/sql-functions/array-functions/array_contains.md • release_notes/release-3.1.md • sql-reference/sql-functions/array-functions/array_contains_all.md References • sql-reference/sql-functions/array-functions/array_contains_all.md • sql-reference/sql-functions/array-functions/array_contains.md • sql-reference/sql-functions/array-functions/any_match.md
2 Views
Open in Slack
PreviousNext
Powered by