Vijay Sarathy created ASTERIXDB-3479:
----------------------------------------
Summary: Enumeration and reordering of UNNEST operator
Key: ASTERIXDB-3479
URL: https://issues.apache.org/jira/browse/ASTERIXDB-3479
Project: Apache AsterixDB
Issue Type: Improvement
Components: COMP - Compiler
Reporter: Vijay Sarathy
In CH2 q3,
{code:java}
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;
{code}
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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
