[
https://issues.apache.org/jira/browse/PHOENIX-3753?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15942739#comment-15942739
]
shenzongqiang edited comment on PHOENIX-3753 at 3/27/17 10:18 AM:
------------------------------------------------------------------
The issus is caused by the salt index affects the query plan of the non-salt
table.
When we look into following line 360 in QueryOptimizer.orderPlansBestToWorst
method,we can see boundCount1 and boundCount2 is both 1.
int boundCount1 = plan1.getContext().getScanRanges().getBoundPkColumnCount();
int boundCount2 = plan2.getContext().getScanRanges().getBoundPkColumnCount();
The PK of the plan1 is {salted, agent_id, date, cust_id}, boundCount1 is 1
because salt Column.
The PK of the plan2 is {date, cust_id}, boundCount2 is 1 because date Column.
In fact, the salt Column should not be included in the calculation, but it will
be used for follow-up inquiries. So, if the two plans to bound the same length
of the primary key, you can increase the judgment whether to add salt. eg:
if (c != 0) {
return c;
} else {
if ((table2.getBucketNum() != null && table2.getBucketNum() > 0) &&
(table1.getBucketNum() == null || table1.getBucketNum() == 0)) {
return -1;
} else if ((table2.getBucketNum() == null || table2.getBucketNum() == 0) &&
(table1.getBucketNum() != null && table1.getBucketNum() > 0)) {
return 1;
}
}
was (Author: allen007):
When selecting the optimal query plan, if the length of the bound PK column is
the same, you should determine whether salted
> The salt index affects the query plan of the non-salt table
> -----------------------------------------------------------
>
> Key: PHOENIX-3753
> URL: https://issues.apache.org/jira/browse/PHOENIX-3753
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.9.0
> Reporter: shenzongqiang
> Attachments: PHOENIX-3753_v1.patch
>
>
> The salt index affects the query plan of the non-salt table,
> Can be reproduced by the following example:
> 1.Create table:
> CREATE TABLE IF NOT EXISTS test1 (
> date varchar NOT NULL,
> cust_id INTEGER NOT NULL,
> agent_id INTEGER,
> pay BIGINT,
> click BIGINT,
> pv BIGINT,
> CONSTRAINT pk PRIMARY KEY (date, cust_id)
> );
> 2.Create index:
> CREATE INDEX ix_test1 ON test1(agent_id) INCLUDE (pay, click) SALT_BUCKETS =
> 10;
> 3.Explain query:
> EXPLAIN SELECT SUM(pay), SUM(click) FROM test1 WHERE DATE = '2017-01-01';
> 4.Expected result:
> +---------------------------------------------------------------------+
> | PLAN |
> +---------------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER TEST1 ['2017-01-01'] |
> | SERVER AGGREGATE INTO SINGLE ROW |
> +---------------------------------------------------------------------+
> 5.Actual results:
> +---------------------------------------------------------------------+
> | PLAN |
> +---------------------------------------------------------------------+
> | CLIENT 10-CHUNK PARALLEL 10-WAY RANGE SCAN OVER IX_TEST1 [0] - [9] |
> | SERVER FILTER BY "DATE" = '2017-01-01' |
> | SERVER AGGREGATE INTO SINGLE ROW |
> +---------------------------------------------------------------------+
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
