Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
This is a followup to MDEV-28073.
Problem description
Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes.
An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit.
The fix for MDEV-28073 addresses these issues to some extent and was pushed into 10.6. This MDEV is about more comprehensive fix.
Solution
TODO description. Copy from:
https://jira.mariadb.org/browse/MDEV-28073?focusedCommentId=225701&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-225701
Join prefix pruning is now controlled by @@optimizer_prune_level variable. The new pruning is done when @@optimizer_prune_level=2, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting @@optimizer_prune_level=1 (the default before 10.10).
The tree
It is preview-10.10-MDEV-28852-join-pruning
Attachments
Issue Links
- causes
-
MDEV-28928 Plan selection takes 2+ times longer than before MDEV-28852
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
(2 relates to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Description |
This is a followup to Join optimizer can be very slow when there are a lot of similar plans to enumerate. |
This is a followup to Join optimizer can be very slow when the join has many tables and join prefix pruning doesn't manage to prune plans. An important subset is a join where a lot of tables use eq_ref |
| Description |
This is a followup to Join optimizer can be very slow when the join has many tables and join prefix pruning doesn't manage to prune plans. An important subset is a join where a lot of tables use eq_ref |
This is a followup to Join optimizer can be very slow when the join has many tables and join prefix pruning doesn't manage to prune plans. An important subset is a join where a lot of tables use eq_ref access . If these tables are next to each Putting these tables in different order |
| Description |
This is a followup to Join optimizer can be very slow when the join has many tables and join prefix pruning doesn't manage to prune plans. An important subset is a join where a lot of tables use eq_ref access . If these tables are next to each Putting these tables in different order |
This is a followup to Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. |
| Description |
This is a followup to Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. |
This is a followup to h2. Problem description Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. The fix for h2. Solution TODO description. TODO: note that pruning is now controlled by {{@@optimizer_prune_level}} variable. The new pruning is enabled by default, {{@@optimizer_prune_level=2}}. One can disable certain aspects (TODO elaborate) of the new behavior by setting {{@@optimizer_prune_level=1}} |
| Description |
This is a followup to h2. Problem description Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. The fix for h2. Solution TODO description. TODO: note that pruning is now controlled by {{@@optimizer_prune_level}} variable. The new pruning is enabled by default, {{@@optimizer_prune_level=2}}. One can disable certain aspects (TODO elaborate) of the new behavior by setting {{@@optimizer_prune_level=1}} |
This is a followup to h2. Problem description Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. The fix for h2. Solution TODO description. Join prefix pruning is now controlled by {{@@optimizer_prune_level}} variable. The new pruning is done when {{@@optimizer_prune_level=2}}, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting {{@@optimizer_prune_level=1}} (the default before 10.10). |
| Assignee | Michael Widenius [ monty ] | Elena Stepanova [ elenst ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | In Testing [ 10301 ] |
| Description |
This is a followup to h2. Problem description Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. The fix for h2. Solution TODO description. Join prefix pruning is now controlled by {{@@optimizer_prune_level}} variable. The new pruning is done when {{@@optimizer_prune_level=2}}, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting {{@@optimizer_prune_level=1}} (the default before 10.10). |
This is a followup to h2. Problem description Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. The fix for h2. Solution TODO description. Join prefix pruning is now controlled by {{@@optimizer_prune_level}} variable. The new pruning is done when {{@@optimizer_prune_level=2}}, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting {{@@optimizer_prune_level=1}} (the default before 10.10). h2. The tree It is {{preview-10.10- |
| Description |
This is a followup to h2. Problem description Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. The fix for h2. Solution TODO description. Join prefix pruning is now controlled by {{@@optimizer_prune_level}} variable. The new pruning is done when {{@@optimizer_prune_level=2}}, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting {{@@optimizer_prune_level=1}} (the default before 10.10). h2. The tree It is {{preview-10.10- |
This is a followup to h2. Problem description Join optimizer can be very slow when the join has many tables and join prefix pruning feature doesn't manage to prune join prefixes. An important special case is when a subset of join tables are joined with an equi-join condition on a primary key, and so can be joined using eq_ref in any order. Then, query plans that use different permutations of these tables have the same cost. The optimizer will consider all possible permutations. This can take a lot of CPU but provide little benefit. The fix for h2. Solution TODO description. Copy from: https://jira.mariadb.org/browse/MDEV-28073?focusedCommentId=225701&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-225701 Join prefix pruning is now controlled by {{@@optimizer_prune_level}} variable. The new pruning is done when {{@@optimizer_prune_level=2}}, which is the new default. One can disable certain aspects (TODO elaborate) of the new behavior by setting {{@@optimizer_prune_level=1}} (the default before 10.10). h2. The tree It is {{preview-10.10- |
| Link | This issue relates to MDEV-28882 [ MDEV-28882 ] |
| Link |
This issue causes |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue relates to |
| Assignee | Elena Stepanova [ elenst ] | Sergei Golubchik [ serg ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| Assignee | Sergei Golubchik [ serg ] | Sergei Petrunia [ psergey ] |
| Labels | optimizer-feature | Preview_10.10 optimizer-feature |
| Fix Version/s | 10.10.1 [ 27913 ] | |
| Fix Version/s | 10.10 [ 27530 ] |
| Fix Version/s | 11.0.1 [ 28548 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36732 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 36732 ] |
| Zendesk Related Tickets | 143812 136995 |