planner,expression: use constraint propagation in partition pruning by tiancaiamao · Pull Request #8885 · pingcap/tidb

tiancaiamao · 2018-12-29T13:14:10Z

What problem does this PR solve?

Now we can prune some of the to_days function:

CREATE TABLE t1 (
a int(10) unsigned NOT NULL,
b DATETIME NOT NULL,
PRIMARY KEY (a, b)
) PARTITION BY RANGE (TO_DAYS(b))
(PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
PARTITION p20090405 VALUES LESS THAN MAXVALUE);

The partition expression is TO_DAYS(b) < XXX and TO_DAYS(b) >= YYY, it has a function to_days.

EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
id	count	task	operator info
Union_9	9970.00	root	
├─TableReader_12	3323.33	root	data:Selection_11
│ └─Selection_11	3323.33	cop	le(test.t1.b, 2009-04-03)
│   └─TableScan_10	10000.00	cop	table:t1, partition:p20090401, range:[-inf,+inf], keep order:false, stats:pseudo
├─TableReader_15	3323.33	root	data:Selection_14
│ └─Selection_14	3323.33	cop	le(test.t1.b, 2009-04-03)
│   └─TableScan_13	10000.00	cop	table:t1, partition:p20090402, range:[-inf,+inf], keep order:false, stats:pseudo
└─TableReader_18	3323.33	root	data:Selection_17
  └─Selection_17	3323.33	cop	le(test.t1.b, 2009-04-03)
    └─TableScan_16	10000.00	cop	table:t1, partition:p20090403, range:[-inf,+inf], keep order:false, stats:pseudo

From the result we can see that p20090404, p20090405 is pruned.

What is changed and how it works?

Fix those two restrictions mentioned in the issue:

it doesn't support filter conditions that can't be push down to datasource

it doesn't support expressions that can't calculate range

Now prune will consider both push down conditions and the filter conditions in the selection.
Leverage the constraint propagate rule in #8640
to handle expressions (function) that can't calculate range.

There are still two minor problems, but they are not introduced by this commit:

The partition expressions for the first partition is col < const or col is null, current partition pruning can't prune or col is null
Range calculating is still kept in the code, we should get rid of it eventually

Check List

Tests

Unit test
Integration test

This change is

zz-jason · 2019-01-02T02:30:48Z

@tiancaiamao Please add some proper labels. For example, component, type.

tiancaiamao · 2019-01-02T05:35:28Z

This is still WIP, and the changes are based on #8640
@zz-jason

codecov-io · 2019-01-04T07:45:18Z

Codecov Report

❗ No coverage uploaded for pull request base (master@59c7b69). Click here to learn what that means.
The diff coverage is 72.72%.

@@            Coverage Diff            @@
##             master    #8885   +/-   ##
=========================================
  Coverage          ?   67.14%           
=========================================
  Files             ?      372           
  Lines             ?    76968           
  Branches          ?        0           
=========================================
  Hits              ?    51679           
  Misses            ?    20657           
  Partials          ?     4632

Impacted Files	Coverage Δ
planner/core/logical_plans.go	`73.72% <ø> (ø)`
planner/core/rule_predicate_push_down.go	`90.63% <100%> (ø)`
expression/constraint_propagation.go	`73.45% <54.54%> (ø)`
planner/core/rule_partition_processor.go	`74.02% <80.95%> (ø)`

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 59c7b69...89e597f. Read the comment docs.

tiancaiamao · 2019-01-04T08:05:18Z

PTAL @eurekaka @winoros @zz-jason

tiancaiamao · 2019-01-07T02:18:07Z

PTAL @eurekaka @winoros @zz-jason

expression/constraint_propagation.go

planner/core/planbuilder_test.go

planner/core/rule_partition_processor.go

planner/core/partition_pruning_test.go

tiancaiamao · 2019-01-08T00:49:25Z

PTAL @eurekaka @zz-jason

eurekaka

LGTM

expression/constraint_propagation.go

planner/core/logical_plans.go

planner/core/planbuilder_test.go

planner/core/rule_partition_processor.go

zz-jason · 2019-01-08T04:15:02Z

planner/core/rule_partition_processor.go

+		}
+	}
+
+	if partCol == nil {


can we check partCol == nil first when entering this function?

Do you mean check partCol == nil at the beginning of this function and return false? No we can't do that.

In to_days(c) > xx and c < yy, partCol is nil, but I want to handle it.
If this branch moved to the beginning, the canBePruned will return immediately.

Got it. Could you add a comment about this to let others know why we intentionally put this check after solver.Solve()?

planner/core/rule_partition_processor.go

zz-jason

LGTM

planner/core/partition_pruning_test.go

tiancaiamao · 2019-01-15T03:09:00Z

/run-all-tests

tiancaiamao · 2019-01-15T03:10:56Z

/run-unit-test

tiancaiamao · 2019-01-15T03:17:28Z

/run-all-tests

tiancaiamao · 2019-01-15T03:38:19Z

PTAL @winoros @zz-jason

tiancaiamao · 2019-01-16T05:52:26Z

PTAL @winoros

tiancaiamao · 2019-01-17T02:40:06Z

PTAL @winoros @zz-jason

tiancaiamao · 2019-01-17T02:54:42Z

/run-all-tests

tiancaiamao added 9 commits December 11, 2018 10:45

expression: constraint propagate for '>' and monotonous function

6cbf528

Merge branch 'master' into propagate-pruning

f966b75

Merge master

e11f0ed

gofmt

12d9212

fix CI

3376571

fix CI

9a3659b

address comment

b3287fc

Merge branch 'master' into propagate-pruning

0f08c3e

planner,expression: use constraint propagation in partition pruning

934bddd

tiancaiamao added the status/WIP label Dec 29, 2018

tiancaiamao added 3 commits January 2, 2019 11:43

make the code more general and remove useless part

bdcf799

Merge branch 'propagate-pruning' into re-partition-prune

c661aa7

update test

c3bddb8

tiancaiamao added sig/planner SIG: Planner component/expression labels Jan 2, 2019

tiancaiamao added 3 commits January 4, 2019 14:17

Merge branch 'master' into re-partition-prune

1939df5

handle filter conditions that do not push down in partition pruning

32dae16

update partition pruning test in explain

4a5735e

tiancaiamao removed the status/WIP label Jan 4, 2019

zz-jason requested review from eurekaka and zz-jason January 6, 2019 07:32

zz-jason reviewed Jan 7, 2019

View reviewed changes

eurekaka reviewed Jan 7, 2019

View reviewed changes

planner/core/rule_partition_processor.go Outdated Show resolved Hide resolved

planner/core/rule_partition_processor.go Outdated Show resolved Hide resolved

planner/core/rule_partition_processor.go Outdated Show resolved Hide resolved

address comment

bf90c2a

alivxxx reviewed Jan 7, 2019

View reviewed changes

planner/core/partition_pruning_test.go Outdated Show resolved Hide resolved

tiancaiamao added 3 commits January 7, 2019 20:51

fix ci

10fb9b1

address comment

084ce7a

address comment

8518cc0

eurekaka reviewed Jan 8, 2019

View reviewed changes

eurekaka added the status/LGT1 Indicates that a PR has LGTM 1. label Jan 8, 2019

zz-jason reviewed Jan 8, 2019

View reviewed changes

address comment

03aff65

zz-jason reviewed Jan 11, 2019

View reviewed changes

zz-jason requested a review from winoros January 11, 2019 08:29

winoros reviewed Jan 14, 2019

View reviewed changes

planner/core/partition_pruning_test.go Outdated Show resolved Hide resolved

address comment

5bec8fc

Merge branch 'master' into re-partition-prune

7d57f56

tiancaiamao added the status/all tests passed label Jan 15, 2019

tiancaiamao added 3 commits January 15, 2019 15:59

fix typo

0a52d72

Merge branch 'master' into re-partition-prune

5f5acb1

address comment

1b0825b

zz-jason approved these changes Jan 17, 2019

View reviewed changes

Merge branch 'master' into re-partition-prune

89e597f

tiancaiamao merged commit b339c02 into pingcap:master Jan 17, 2019

tiancaiamao deleted the re-partition-prune branch January 17, 2019 03:09

tiancaiamao mentioned this pull request Feb 7, 2020

*: re-implement partition pruning for better performance #14679

Merged

Conversation

tiancaiamao commented Dec 29, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What problem does this PR solve?

What is changed and how it works?

Check List

Uh oh!

zz-jason commented Jan 2, 2019

Uh oh!

tiancaiamao commented Jan 2, 2019

Uh oh!

codecov-io commented Jan 4, 2019 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

tiancaiamao commented Jan 4, 2019

Uh oh!

tiancaiamao commented Jan 7, 2019

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

tiancaiamao commented Jan 8, 2019

Uh oh!

eurekaka left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

zz-jason Jan 8, 2019

Choose a reason for hiding this comment

Uh oh!

tiancaiamao Jan 8, 2019

Choose a reason for hiding this comment

Uh oh!

zz-jason Jan 8, 2019

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

zz-jason left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

tiancaiamao commented Jan 15, 2019

Uh oh!

tiancaiamao commented Jan 15, 2019

Uh oh!

tiancaiamao commented Jan 15, 2019

Uh oh!

tiancaiamao commented Jan 15, 2019

Uh oh!

tiancaiamao commented Jan 16, 2019

Uh oh!

tiancaiamao commented Jan 17, 2019

Uh oh!

tiancaiamao commented Jan 17, 2019

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

tiancaiamao commented Dec 29, 2018 •

edited

Loading

codecov-io commented Jan 4, 2019 •

edited

Loading

zz-jason left a comment •

edited

Loading