[jira] [Commented] (IMPALA-7942) Add query hints for cardinalities and selectivities

["ASF subversion and git services (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IMPALA-7942?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714649#comment-17714649 ] 

ASF subversion and git services commented on IMPALA-7942:
---------------------------------------------------------

Commit 563d5c50b82ed0765547aa7b57e0a04822b9e484 in impala's branch refs/heads/master from skyyws
[ https://gitbox.apache.org/repos/asf?p=impala.git;h=563d5c50b ]

IMPALA-7942 (part 2): Add query hints for predicate selectivities

Currently, Impala only uses simple estimation to compute selectivity.
For some predicates, this may lead to worse query plan due to CBO.

This patch adds a new query hint: 'SELECTIVITY' to help specify a
selectivity value for a predicate.

The parser will interpret expressions wrapped in () followed by a
C-style comment /* <predicate hint> */ as a predicate hint. The
predicate hint currently can be in the form of +SELECTIVITY(f) where
'f' is a positive floating point number, in the range of (0, 1], to
use as the selectivity for the preceding expression.

Single predicate example:

  select col from t where (a=1) /* +SELECTIVITY(0.5) */;

Compound predicate example:

  select col from t where (a=1 or b=2) /* +SELECTIVITY(0.5) */;

As a limitation of this path, the selectivity hints for 'AND' compound
predicates, either in the original SQL query or internally generated,
are ignored. We may supported this in the near future.

Testing:
- Added new fe tests in 'PlannerTest'
- Added new fe tests in 'AnalyzeStmtsTest' for negative cases

Change-Id: I2776b9bbd878b8a21d9c866b400140a454f59e1b
Reviewed-on: http://gerrit.cloudera.org:8080/18023
Tested-by: Impala Public Jenkins <im...@cloudera.com>
Reviewed-by: Qifan Chen <qf...@hotmail.com>


> Add query hints for cardinalities and selectivities
> ---------------------------------------------------
>
>                 Key: IMPALA-7942
>                 URL: https://issues.apache.org/jira/browse/IMPALA-7942
>             Project: IMPALA
>          Issue Type: New Feature
>          Components: Frontend
>    Affects Versions: Impala 3.2.0
>            Reporter: Lars Volker
>            Assignee: Sheng Wang
>            Priority: Major
>
> The optimizer can pick suboptimal plans when tables don't have statistics. To allow users to help the optimizer, we should support query hints to specify cardinalities of scans, predicated (and possibly joins).
> This could look like the following example.
> {code:sql}
> select x from medium /*+ num_rows(1000000000) */
>   join small /*+ num_rows(1000000) */
>   join (select * from big /*+ num_rows(1000000000) */
>         where c1 < 10 /*+ selectivity(0.00001) */) as big
>   where medium.id = small.id and small.id = big.id;
> {code}
> Instead of cardinalities we could also support specifying the number of rows that pass a predicate (or join).
> We should not rely on the specified cardinalities to be accurate, e.g. the following should still execute a scan:
> {code:sql}
> select count(*) from T /*+ num_rows(100) */
>   where id < 100 /*+ selectivity(0.1) */;
> {code}
> This is a first step towards giving users more control over the planner / optimizer.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org