[ASTERIXDB-3479] Enumeration and reordering of UNNEST operator

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: COMP - Compiler
• Labels: triaged
• Assignee: Unassigned
• Reporter: Vijay Sarathy
• Created: 09/Aug/24 15:53
• Updated: 09/Aug/24 17:05

Description

In CH2 q3,

SELECT o.o_id, o.o_w_id, o.o_d_id, SUM(ol.ol_amount) AS revenue, o.o_entry_d
FROM   customer c, neworder no, orders o, o.o_orderline ol
WHERE  c.c_state LIKE 'A%'
  AND c.c_id = o.o_c_id
  AND c.c_w_id = o.o_w_id
  AND c.c_d_id = o.o_d_id
  AND no.no_w_id = o.o_w_id
  AND no.no_d_id = o.o_d_id
  AND no.no_o_id = o.o_id
  AND o.o_entry_d < '2017-03-15 00:00:00.000000'
GROUP BY o.o_id, o.o_w_id, o.o_d_id, o.o_entry_d
ORDER BY revenue DESC, o.o_entry_d;

the filter on orders is not very selective and returns ~50% of the rows, but the filter on customer is much more selective, and the C-O join is therefore much smaller (about 6% of the size of O). Since there are no references to the unnested part of O (except in the select clause), the unnest on O can be moved up after the C-O join or even after the final join. This will improve performance quite a bit. CBO needs to treat UNNEST like a "local join" and be able to enumerate and reorder it like regular join operators.