I have a query running in presto which has array_intersect condition. This is taking around 5 hrs to run. If I remove the array_intersect then it is taking less than an hour.
CARDINALITY(ARRAY_INTERSECT(links, ARRAY['504949547', '504949616', '515604515', '515604526', '515604527', '515604528'])) > 0
Can anyone please let me know how to improve the performance. Have to get it less than 5 mins.
Have tried enabling the spill disk but it didnt help. Input data size is around 1TB.
Thanks
1 Answer
array_intersect materializes the result (the intersection), whereas the only thing you are checking is the membership of certain predefined elements.
In this case I'd recommend using any_match instead.
any_match(links, e -> e IN ('504949547', '504949616', ...))
If you're using Presto versions that doesn't have any_match, you can use reduce:
reduce(
links, -- array to reduce
false, -- initial state
(s, e) -> s OR e IN ('504949547', '504949616', ...), -- reduction function
s -> s) -- output function
Have tried enabling the spill disk but it didnt help.
Note: In Presto, spill is supported for certain operators (most of the Joins, Aggregations, Order By, Window functions). It is not applicable to scalar functions operating on ARRAYs. Also, you should not expect spill to increase performance. It can only reduce memory footprint, at the cost of performance.
