MySQL Bugs: #90951: Contribution by Facebook: Enhance group-by loose index scan

Bug #90951	Contribution by Facebook: Enhance group-by loose index scan
Submitted:	21 May 2018 16:18	Modified:	29 Oct 2019 23:52
Reporter:	FBContrib Admin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Background innformation provided by Facebook:
Abstract:

This enhances the current group by plan so that every "gap attribute" is allowed to have a disjunction of equality predicates. The predicates from different attributes must still be conjunctive to each other though, and this is all enforced in check_key_infix.

These were high level implementation points:
- Change get_constant_key_infix to check_key_infix, which no longer extracts the constant key infix because it may not exist. Instead, it just checks conditions NGA1 and NGA2.
- Instead the key ranges are extracted when the QUICK_SELECT_I plan is created after cost estimation. They are extracted into a DYANMIC_ARRAY of QUICK_RANGEs, similar to how MIN/MAX ranges are currently implemented.
- QUICK_GROUP_MIN_MAX_SELECT::get_next is extended so that all permutations of the equality predicates on the gap attribute are iterated through for every group. This logic most resides in append_next_infix.
- The cost model is updated so that the number of "groups" is multiplied by the total number of permutations of equality predicates over the gap attributes.

Currently, we still only allow equality predicates over gap attributes, but it can be extended into arbitrary ranges. The use of QUICK_RANGE should make that extension fairly easy.

Repo: https://github.com/mysql/mysql-server
Patch on top of 5.6.35: https://github.com/mysql/mysql-server/tree/d61eb03ba1b8478d6cb09969ca6c38f330a93178
Facebook commit: https://github.com/facebook/mysql-5.6/commit/60a92a7

How to repeat:
See description

Suggested fix:
See contribution code attached

Enhance group-by loose index scan 
(*) This code is contributed under the Facebook agreement

Contribution: fb_patch_64.txt (text/plain), 103.12 KiB.

Thanks for the patch, contribution is accepted and will be incorporated into
MySQL in near future.

Enhance group-by loose index scan - Update 
(*) This code is contributed under the Facebook agreement

Contribution: fb_patch_117.txt (text/plain), 4.81 KiB.

Documented enhancement in the MySQL 8.0.19 changelog as follows:

    The current GROUP BY plan is improved so that every gap
    attribute is allowed to have a disjunction of equality
    predicates. Predicates from different attributes must still be
    conjunctive to each other in order to take advantage of this
    enhancement.

    Our thanks to Facebook for this contribution.

Closed.