[jira] [Updated] (ASTERIXDB-1984) Index-Nested-Loop Join should not care about the contents of the probe branch

["Taewoo Kim (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/ASTERIXDB-1984?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Taewoo Kim updated ASTERIXDB-1984:
----------------------------------
    Description: 
Currently, when the optimizer tries to transform a query with the "index_nl" hint, it tries to identify datasource, assign, and unnest operators in both outer (probe) and inner branch. It also tries to identify the fieldname of the variables that are being joined. However, the probe branch can be an arbitrary sub-plan and what only really matters for the probe subtree is the type of the field from the probe tree that is being joined. If that field type is correctly identified, then any form of probe-subtree with a simple data-scan on the inner branch can be correctly transformed into an index-utilization plan. The following queries should be transformed into an index-utilization plan. However, they are not transformed now in the current master.

E.g.,

{code}
SELECT * FROM
[1, 2, 3] AS bar JOIN foo on bar /*+ indexnl */ =  foo.key;


SELECT  * FROM
bar JOIN foo on bar.id = foo.key JOIN datac ON foo.key /*+ indexnl */ = datac.val;


SELECT  * FROM
(SELECT id, COUNT(*) FROM bar GROUP BY id) AS barr JOIN foo ON barr.id /*+ indexnl */ =  foo.key;
{code}

  was:
Currently, when the optimizer tries to transform a query with the "index_nl" hint, it tries to identify datasource, assign, and unnest operators in both outer (probe) and inner branch. It also tries to identify the fieldname of the variables that are being joined. However, the probe branch can be an arbitrary sub-plan and what only really matters for the probe subtree is the type of the field from the probe tree that is being joined. If that field type is correctly identified, then any form of probe-subtree with a simple data-scan on the inner branch can be correctly transformed into an index-utilization plan. The following queries should be transformed into an index-utilization plan. However, they are not transformed now in the current master.

E.g.,

{code}
SELECT * FROM
[1, 2, 3] AS bar JOIN foo on bar = /*+ indexnl */ foo.key;


SELECT  * FROM
bar JOIN foo on bar.id = foo.key JOIN datac ON foo.key = /*+ indexnl */ datac.val;


SELECT  * FROM
(SELECT id, COUNT(*) FROM bar GROUP BY id) AS barr JOIN foo ON barr.id= /*+ indexnl */ foo.key;
{code}



> Index-Nested-Loop Join should not care about the contents of the probe branch
> -----------------------------------------------------------------------------
>
>                 Key: ASTERIXDB-1984
>                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-1984
>             Project: Apache AsterixDB
>          Issue Type: Bug
>            Reporter: Taewoo Kim
>            Assignee: Taewoo Kim
>
> Currently, when the optimizer tries to transform a query with the "index_nl" hint, it tries to identify datasource, assign, and unnest operators in both outer (probe) and inner branch. It also tries to identify the fieldname of the variables that are being joined. However, the probe branch can be an arbitrary sub-plan and what only really matters for the probe subtree is the type of the field from the probe tree that is being joined. If that field type is correctly identified, then any form of probe-subtree with a simple data-scan on the inner branch can be correctly transformed into an index-utilization plan. The following queries should be transformed into an index-utilization plan. However, they are not transformed now in the current master.
> E.g.,
> {code}
> SELECT * FROM
> [1, 2, 3] AS bar JOIN foo on bar /*+ indexnl */ =  foo.key;
> SELECT  * FROM
> bar JOIN foo on bar.id = foo.key JOIN datac ON foo.key /*+ indexnl */ = datac.val;
> SELECT  * FROM
> (SELECT id, COUNT(*) FROM bar GROUP BY id) AS barr JOIN foo ON barr.id /*+ indexnl */ =  foo.key;
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)