[jira] [Created] (ASTERIXDB-2924) Three-way joins with same probe using INLJ

["Glenn Justo Galvizo (Jira)" <ji...@apache.org>]

Glenn Justo Galvizo created ASTERIXDB-2924:
----------------------------------------------

             Summary: Three-way joins with same probe using INLJ
                 Key: ASTERIXDB-2924
                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2924
             Project: Apache AsterixDB
          Issue Type: Improvement
          Components: IDX - Indexes
            Reporter: Glenn Justo Galvizo
            Assignee: Glenn Justo Galvizo


Given the DDLs:
{code:java}
DROP DATAVERSE    TestDataverse IF EXISTS;
CREATE DATAVERSE  TestDataverse;
USE               TestDataverse;

CREATE TYPE       GenericType AS { _id: bigint, c : bigint };
CREATE DATASET    IndexDatasetA (GenericType)
PRIMARY KEY       _id;
CREATE DATASET    IndexDatasetB (GenericType)
PRIMARY KEY       _id;
CREATE DATASET    ProbeDataset (GenericType)
PRIMARY KEY       _id;

CREATE INDEX      indexA
ON                IndexDatasetA (k : int);
CREATE INDEX      indexB
ON                IndexDatasetB (k : int);{code}
The following 3-way join query produces an INLJ plan for both indexed datasets. The join field from the probe is the probe dataset's primary key.
{code:java}
--                Query 1, 3-way join w/ primary key on probe.
FROM              ProbeDataset P,
                  IndexDatasetA A,
                  IndexDatasetB B
WHERE             P._id /* +indexnl */ = A.k AND
                  P._id /* +indexnl */ = B.k
SELECT            COUNT(*);{code}
The following 3-way join query produces an INLJ plan for only the left-most indexed dataset. The join field from the probe is a closed field from the probe dataset.
{code:java}
--                Query 2, 3-way join w/ closed field on probe.
FROM              ProbeDataset P,
                  IndexDatasetA A,
                  IndexDatasetB B
WHERE             P.c /* +indexnl */ = A.k AND
                  P.c /* +indexnl */ = B.k
SELECT            COUNT(*);
{code}
The following 3-way join query produces an no INLJ plans. The join field from the probe is an open field from the probe dataset.
{code:java}
--                Query 3, 3-way join w/ open field on probe.
FROM              ProbeDataset P,
                  IndexDatasetA A,
                  IndexDatasetB B
WHERE             (P.c + 1) /* +indexnl */ = A.k AND
                  (P.c + 1) /* +indexnl */ = B.k
SELECT            COUNT(*);{code}
All three queries should produce two INLJs.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)