You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by "Xikui Wang (Jira)" <ji...@apache.org> on 2019/11/16 17:20:00 UTC
[jira] [Updated] (ASTERIXDB-2676) Index access method ignores condition containing function calls

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

Xikui Wang updated ASTERIXDB-2676:
----------------------------------
    Description: 
Given the following two datasets
{code}
CREATE TYPE Tweet AS OPEN {
 tid: bigint,
 area_code: string,
 text: string,
 location: point,
 hateful_rating: int,
 timestamp: datetime
};
CREATE DATASET Tweets(Tweet) PRIMARY KEY tid;

CREATE TYPE OfficerLocation AS OPEN {
  oid: bigint,
  location: point
};
CREATE DATASET OfficerLocations(OfficerLocation) PRIMARY KEY oid;

CREATE INDEX s_location ON Tweets(location) type RTREE;
CREATE INDEX o_location ON OfficerLocations(location) type RTREE;
{code}

The 1st query won't be able to utilize index, and a user would have to explicitly create a new variable like in the 2nd query to be able to use it. The reason is in IntroduceJoinAccessMethodRule, the case in which one of the parameters is function call is ignored when considering utilize indexes.

{code}
  SELECT t
  FROM Tweets t, OfficerLocations o
  WHERE /*+ indexnl */ spatial_intersect(create_circle(t.location, 100), o.location);
{code}

{code}
  SELECT t
  FROM Tweets t, OfficerLocations o
  LET area = create_circle(t.location, 100)
  WHERE /*+ indexnl */ spatial_intersect(area, o.location);
{code}

  was:
Given the following two datasets
{code}
CREATE TYPE Tweet AS OPEN {
 tid: bigint,
 area_code: string,
 text: string,
 location: point,
 hateful_rating: int,
 timestamp: datetime
};
CREATE DATASET Tweets(Tweet) PRIMARY KEY tid;

CREATE TYPE OfficerLocation AS OPEN {
  oid: bigint,
  location: point
};
CREATE DATASET OfficerLocations(OfficerLocation) PRIMARY KEY oid;

CREATE INDEX s_location ON Tweets(location) type RTREE;
CREATE INDEX o_location ON OfficerLocations(location) type RTREE;
{code}

The 1st query won't be able to utilize index, and a user would have to explicitly create a new variable like in the 2nd query to be able to use it. The reason is in IntroduceJoinAccessMethodRule, the case in which one of the parameters is function call is ignored when considering utilize indexes.

{code}
  SELECT t
  FROM Tweets t, OfficerLocations o
  WHERE /*+ indexnl */ spatial_intersect(create_circle(t.location, 100), o.location) AND is_new(t) AND is_new(o);
{code}

{code}
  SELECT t
  FROM Tweets t, OfficerLocations o
  LET area = create_circle(t.location, 100)
  WHERE /*+ indexnl */ spatial_intersect(area, o.location) AND is_new(t) AND is_new(o);
{code}


> Index access method ignores condition containing function calls
> ---------------------------------------------------------------
>
>                 Key: ASTERIXDB-2676
>                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2676
>             Project: Apache AsterixDB
>          Issue Type: Improvement
>          Components: COMP - Compiler
>            Reporter: Xikui Wang
>            Priority: Major
>
> Given the following two datasets
> {code}
> CREATE TYPE Tweet AS OPEN {
>  tid: bigint,
>  area_code: string,
>  text: string,
>  location: point,
>  hateful_rating: int,
>  timestamp: datetime
> };
> CREATE DATASET Tweets(Tweet) PRIMARY KEY tid;
> CREATE TYPE OfficerLocation AS OPEN {
>   oid: bigint,
>   location: point
> };
> CREATE DATASET OfficerLocations(OfficerLocation) PRIMARY KEY oid;
> CREATE INDEX s_location ON Tweets(location) type RTREE;
> CREATE INDEX o_location ON OfficerLocations(location) type RTREE;
> {code}
> The 1st query won't be able to utilize index, and a user would have to explicitly create a new variable like in the 2nd query to be able to use it. The reason is in IntroduceJoinAccessMethodRule, the case in which one of the parameters is function call is ignored when considering utilize indexes.
> {code}
>   SELECT t
>   FROM Tweets t, OfficerLocations o
>   WHERE /*+ indexnl */ spatial_intersect(create_circle(t.location, 100), o.location);
> {code}
> {code}
>   SELECT t
>   FROM Tweets t, OfficerLocations o
>   LET area = create_circle(t.location, 100)
>   WHERE /*+ indexnl */ spatial_intersect(area, o.location);
> {code}



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