You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@impala.apache.org by "Paul Rogers (JIRA)" <ji...@apache.org> on 2018/12/31 20:33:00 UTC

[jira] [Created] (IMPALA-8037) Planner makes poor estimations for non-equality predicates

Paul Rogers created IMPALA-8037:
-----------------------------------

             Summary: Planner makes poor estimations for non-equality predicates
                 Key: IMPALA-8037
                 URL: https://issues.apache.org/jira/browse/IMPALA-8037
             Project: IMPALA
          Issue Type: Bug
          Components: Frontend
    Affects Versions: Impala 3.1.0
            Reporter: Paul Rogers
            Assignee: Paul Rogers


Consider the TPC-H queries. Here is a part of one of them:

{code:sql}
select
  count(*)
from tpch.customer c,
  tpch.orders o
where
  c.c_mktsegment = 'BUILDING'
  and c.c_custkey = o.o_custkey
  and o_orderdate < '1995-03-15'
{code}

This produces the following cardinality estimates (using the revised, standard join cardinality estimator):

{noformat}
| 02:HASH JOIN [INNER JOIN, BROADCAST]                                      |
| |  hash predicates: o.o_custkey = c.c_custkey                             |
| |  runtime filters: RF000 <- c.c_custkey                                  |
| |  row-size=59B cardinality=30.00K                                        |
| |                                                                         |
| |--04:EXCHANGE [BROADCAST]                                                |
| |  |                                                                      |
| |  00:SCAN HDFS [tpch.customer c]                                         |
| |     partitions=1/1 files=1 size=23.08MB row-size=29B cardinality=30.00K |
| |     predicates: c.c_mktsegment = 'BUILDING'                             |
| |                                                                         |
| 01:SCAN HDFS [tpch.orders o]                                              |
|    partitions=1/1 files=1 size=162.56MB row-size=30B cardinality=150.00K  |
|    predicates: o_orderdate < '1995-03-15'                                 |
|    runtime filters: RF000 -> o.o_custkey                                  |
{noformat}

Run the query, the actual count is 147K vs. the estimate of 30K. The culprit here is the HDFS Scan node with an estimated cardinality of 150K. Run just this query:

{code:sql}
select count(*)
from tpch.orders o
where
  o_orderdate < '1995-03-15'
{code}

With this plan:

{noformat}
| 00:SCAN HDFS [tpch.orders o]                                             |
|    partitions=1/1 files=1 size=162.56MB row-size=22B cardinality=150.00K |
|    predicates: o_orderdate < '1995-03-15'                                |
{noformat}

With with an actual result of 727K. Had this been used in the previous plan instead of 30K, the join cardinality would have been about four times larger, or 120K, which is far closer to the actual total.

So, the problem is the mis-estimation of the inequality, which are very hard to estimate. The actual selectivity, found by running a {{count\(*)}} on the table, is 727K / 1.5M =~ 0.5. But, Impala estimated 30K / 1.5M = 0.02. Most systems estimate around 0.45 or so.

For example, using a default estimate of 1/3 for inequality provides the following plan, which is much better:

{noformat}
03:HASH JOIN [INNER JOIN]
|  hash predicates: c.c_custkey = o.o_custkey
|  runtime filters: RF002 <- o.o_custkey
|  row-size=71B cardinality=120.00K
|
|--01:SCAN HDFS [tpch.orders o]
|     partitions=1/1 files=1 size=162.56MB row-size=42B cardinality=600.00K
|     predicates: o_orderdate < '1995-03-15'
|     runtime filters: RF000 -> o.o_orderkey
{noformat}

Makes one wonder if the data set was designed with the industry-standard defaults in mind...



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)