[ 
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)

Reply via email to