- developers - lists.mariadb.org

[Maria-developers] Updated (by Guest): index_merge: non-ROR intersection (21)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge: non-ROR intersection CREATION DATE..: Thu, 21 May 2009, 21:32 SUPERVISOR.....: Knielsen IMPLEMENTOR....: COPIES TO......: Psergey CATEGORY.......: Server-RawIdeaBin TASK ID........: 21 (http://askmonty.org/worklog/?tid=21) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Tue, 26 May 2009, 14:04)=-=- High-Level Specification modified. --- /tmp/wklog.21.old.1802 2009-05-26 14:04:57.000000000 +0300 +++ /tmp/wklog.21.new.1802 2009-05-26 14:04:57.000000000 +0300 @@ -1,4 +1,3 @@ - <contents> 1. Execution 1.1 Temptable @@ -30,6 +29,8 @@ 1.1 Temptable ------------- +[ This is our strategy of choice at the moment] + Use a temporary heap-grow-out-to-myisam table with a primary key: create table temp_table ( @@ -168,3 +169,8 @@ a subset of columns covered by all other indexes. = (TODO any other rules?) +- Correlation across selectivities. If there is a condition + + "cond(key1) AND cond(key2) AND ... AND cond(keyN)", + + can we consider satisfaction of AND-parts to be independent? -=-=(Psergey - Thu, 21 May 2009, 21:33)=-=- High-Level Specification modified. --- /tmp/wklog.21.old.25705 2009-05-21 21:33:02.000000000 +0300 +++ /tmp/wklog.21.new.25705 2009-05-21 21:33:02.000000000 +0300 @@ -1 +1,170 @@ +<contents> +1. Execution +1.1 Temptable +1.1.1 Improvement +1.2 Produce/merge sorted streams +1.3 Extend Unique class to handle intersection +1.4 Strategies that do not seem to be useful +1.4.1 Remove matches after having produced an ordered stream +1.4.2 Sparse rowid bitmaps +2. Optimization + +</contents> + +1. Execution +============ + +The primary task is to find means to compute an intersection of N unordered +streams. Besides general memory/cpu cost of computation, we consider: + +- whether the produced rowid stream is ordered. If it is, it can be piped + into index_merge/intersect (as opposed to sort-intersect) + +- whether the strategy can take advantage of the fact that some input streams + are already rowid-ordered + +- startup cost (cost of producing the first output record) + +We see the following possible strategies: + +1.1 Temptable +------------- +Use a temporary heap-grow-out-to-myisam table with a primary key: + +create table temp_table ( + rowid binary($rowid_size), + count n, + primary key(rowid); +); + +Then use this algorithm: + + i1= {index with the least E(#records)}; + + for each record R in range_scan(i1) + temp_table.insert(R.rowid, count=1); + + for each index idx except i1 + { + for each R record in scan(idx) // (INNER-LOOP) + { + if (temp_table has R) + temptable[R].count++; + } + } + + // The following loop can do ordered or unordered scan + // if we want it to be ordered scan, we probably better arrange so that + // 'count' column is part of the index. + for each record R in temp_table + { + if (R.count == number_of_streams) + emit(R.rowid); + } + +The algorithm has an option to emit an ordered rowid stream. + +In the above form, the cost to produce the first record is high. It's easy to +adjust the algorithm to make it low - we'll need to just start scanning all +indexes at once, and finish as soon as we got a full match, i.e. the + + temptable[R].count++ + +operation resulted in the counter being equal to the number of merged scans. + +1.1.1 Improvement +~~~~~~~~~~~~~~~~~ +When running INNER-LOOP, we could count how many times we've done the +"count++" operation. If it has been done #records-in-temptable times, that +means that all further records will not have matches and we can finish the +scan, i.e. break out of the INNER-LOOP. + +1.2 Produce/merge sorted streams +-------------------------------- +For each of the merged scan, use filesort-like action to end up with an +ordered stream of rowids. Then merge the ordered streams. + +By filesort-like action we mean + - Run over index, collect rowids in a buffer. + - When the buffer is full, sort it and dump into a temporary file. +After the above we'll end up with a number of sorted buffers on disk. We can +use mergebuff() function (it is part of filesort's functions) to produce one +ordered sequence (i.e. array, which may be partially on disk) of rowids. + +Merging of ordered streams with help of priority queue is already implemented +in QUICK_ROR_INTERSECT_SELECT. We'll need to substitute the + + child_quick->get_next() + +call with a call to read rowid from an ordered sequence. + +1.3 Extend Unique class to handle intersection +---------------------------------------------- +There is no point to use Unique object as a device that accumulates rowids of +a single scan then produces them in sorted order. One could do the same faster +with accumulating an array of rowids and then sorting it. + +It's possible to use Unique object to collect/merge data from all scans though. +The idea is as follows: + +- Unique should store <rowid, n_scans> pairs +- Duplicates are pairs with the same rowid +- Unique should try to avoid creating duplicates: + - don't add a duplicate into the in-memory part, instead combine two elements + together by adding their n_scans elements. + - combine duplicates when it sees them in Unique.get() call +- The data we get from Unique.get() should be filtered, all records that have + n_scans != number_of_scans_being_merged should be discarded. + +If we're lucky to have started and finished a scan on some index (denote it +as S) without flushing the Unique in the process, then: +- there is no point in adding any new records into the Unique because their + absence in the Unique means that they don't have match in S and hence will + not get into the result of intersection. +- we need to only update the counters to be able to tell if the elements that + are already in the Unique will have matches in all scans. + +1.4 Strategies that do not seem to be useful +-------------------------------------------- + +keeping them here so we don't consider them over and over + +1.4.1 Remove matches after having produced an ordered stream +------------------------------------------------------------ +We can dump everything into a rowid stream and get it sorted. Then we read it, +and if we see a rowid repeated $n_merged_scans times, it belongs to the +intersection (pass to output), otherwise it doesn't (skip). +This doesn't have any advantages over the produce/merge sorted streams +approach. + +1.4.2 Sparse rowid bitmaps +-------------------------- +Use Falcon-style rowid bitmaps. The problem with that is that Falcon's +bitmaps assume there will always be enough memory to accommodate them. + +PostgreSQL makes bitmaps "loose" when they exceed certain size by remembering +disk pages, not ids of individual records. It's hard for us to do something +similar because our rowids are opaque entities whose meaning depends on the +storage engines. + +This seems to require too much change to be worth it. + +2. Optimization +=============== + +SEL_TREE objects already represent intersections. The problems with +optimizations are: + +- Cost formula(s) +- When N keys/conditions are present: + + "cond(key1) AND cond(key2) AND ... AND cond(keyN)", + + somehow avoid considering (2^n - n) possible options. + +- Avoid producing (or even considering) apparently suboptimal plans: + = Don't generate a merge of indexes (I_1, ... I_n) where columns of I_n are + a subset of columns covered by all other indexes. + = (TODO any other rules?) + DESCRIPTION: At the moment index_merge supports intersection only for rowid-ordered streams. This translates into a limitation that index_merge/intersect can only be constructed for equality conditions (t.keypart1=const1 AND t.keypart2=const2 AND ... ) and the equalities should cover all index components. This WL entry is to lift this limitation by developing algorithms that do intersection on non-ROR scans. HIGH-LEVEL SPECIFICATION: <contents> 1. Execution 1.1 Temptable 1.1.1 Improvement 1.2 Produce/merge sorted streams 1.3 Extend Unique class to handle intersection 1.4 Strategies that do not seem to be useful 1.4.1 Remove matches after having produced an ordered stream 1.4.2 Sparse rowid bitmaps 2. Optimization </contents> 1. Execution ============ The primary task is to find means to compute an intersection of N unordered streams. Besides general memory/cpu cost of computation, we consider: - whether the produced rowid stream is ordered. If it is, it can be piped into index_merge/intersect (as opposed to sort-intersect) - whether the strategy can take advantage of the fact that some input streams are already rowid-ordered - startup cost (cost of producing the first output record) We see the following possible strategies: 1.1 Temptable ------------- [ This is our strategy of choice at the moment] Use a temporary heap-grow-out-to-myisam table with a primary key: create table temp_table ( rowid binary($rowid_size), count n, primary key(rowid); ); Then use this algorithm: i1= {index with the least E(#records)}; for each record R in range_scan(i1) temp_table.insert(R.rowid, count=1); for each index idx except i1 { for each R record in scan(idx) // (INNER-LOOP) { if (temp_table has R) temptable[R].count++; } } // The following loop can do ordered or unordered scan // if we want it to be ordered scan, we probably better arrange so that // 'count' column is part of the index. for each record R in temp_table { if (R.count == number_of_streams) emit(R.rowid); } The algorithm has an option to emit an ordered rowid stream. In the above form, the cost to produce the first record is high. It's easy to adjust the algorithm to make it low - we'll need to just start scanning all indexes at once, and finish as soon as we got a full match, i.e. the temptable[R].count++ operation resulted in the counter being equal to the number of merged scans. 1.1.1 Improvement ~~~~~~~~~~~~~~~~~ When running INNER-LOOP, we could count how many times we've done the "count++" operation. If it has been done #records-in-temptable times, that means that all further records will not have matches and we can finish the scan, i.e. break out of the INNER-LOOP. 1.2 Produce/merge sorted streams -------------------------------- For each of the merged scan, use filesort-like action to end up with an ordered stream of rowids. Then merge the ordered streams. By filesort-like action we mean - Run over index, collect rowids in a buffer. - When the buffer is full, sort it and dump into a temporary file. After the above we'll end up with a number of sorted buffers on disk. We can use mergebuff() function (it is part of filesort's functions) to produce one ordered sequence (i.e. array, which may be partially on disk) of rowids. Merging of ordered streams with help of priority queue is already implemented in QUICK_ROR_INTERSECT_SELECT. We'll need to substitute the child_quick->get_next() call with a call to read rowid from an ordered sequence. 1.3 Extend Unique class to handle intersection ---------------------------------------------- There is no point to use Unique object as a device that accumulates rowids of a single scan then produces them in sorted order. One could do the same faster with accumulating an array of rowids and then sorting it. It's possible to use Unique object to collect/merge data from all scans though. The idea is as follows: - Unique should store <rowid, n_scans> pairs - Duplicates are pairs with the same rowid - Unique should try to avoid creating duplicates: - don't add a duplicate into the in-memory part, instead combine two elements together by adding their n_scans elements. - combine duplicates when it sees them in Unique.get() call - The data we get from Unique.get() should be filtered, all records that have n_scans != number_of_scans_being_merged should be discarded. If we're lucky to have started and finished a scan on some index (denote it as S) without flushing the Unique in the process, then: - there is no point in adding any new records into the Unique because their absence in the Unique means that they don't have match in S and hence will not get into the result of intersection. - we need to only update the counters to be able to tell if the elements that are already in the Unique will have matches in all scans. 1.4 Strategies that do not seem to be useful -------------------------------------------- keeping them here so we don't consider them over and over 1.4.1 Remove matches after having produced an ordered stream ------------------------------------------------------------ We can dump everything into a rowid stream and get it sorted. Then we read it, and if we see a rowid repeated $n_merged_scans times, it belongs to the intersection (pass to output), otherwise it doesn't (skip). This doesn't have any advantages over the produce/merge sorted streams approach. 1.4.2 Sparse rowid bitmaps -------------------------- Use Falcon-style rowid bitmaps. The problem with that is that Falcon's bitmaps assume there will always be enough memory to accommodate them. PostgreSQL makes bitmaps "loose" when they exceed certain size by remembering disk pages, not ids of individual records. It's hard for us to do something similar because our rowids are opaque entities whose meaning depends on the storage engines. This seems to require too much change to be worth it. 2. Optimization =============== SEL_TREE objects already represent intersections. The problems with optimizations are: - Cost formula(s) - When N keys/conditions are present: "cond(key1) AND cond(key2) AND ... AND cond(keyN)", somehow avoid considering (2^n - n) possible options. - Avoid producing (or even considering) apparently suboptimal plans: = Don't generate a merge of indexes (I_1, ... I_n) where columns of I_n are a subset of columns covered by all other indexes. = (TODO any other rules?) - Correlation across selectivities. If there is a condition "cond(key1) AND cond(key2) AND ... AND cond(keyN)", can we consider satisfaction of AND-parts to be independent? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): index_merge optimizer: dont discard index_merge union strategies when range is available (24)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge optimizer: dont discard index_merge union strategies when range is available CREATION DATE..: Tue, 26 May 2009, 12:10 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Client-BackLog TASK ID........: 24 (http://askmonty.org/worklog/?tid=24) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Tue, 26 May 2009, 13:27)=-=- High-Level Specification modified. --- /tmp/wklog.24.old.305 2009-05-26 13:27:32.000000000 +0300 +++ /tmp/wklog.24.new.305 2009-05-26 13:27:32.000000000 +0300 @@ -1 +1,70 @@ +(Not a ready HLS but draft) +<contents> +Solution overview +Limitations +TODO + +</contents> + +Solution overview +================= +The idea is to delay discarding potential index_merge plans until the point +where it is really necessary. + +This way, we won't have to do much changes in the range analyzer, but will be +able to keep potential index_merge plan just enough so that it's possible to +take it into consideration together with range access plans. + +Since there are no changes in the optimizer, the ability to consider both +range and index_merge options will be limited to WHERE clauses of this form: + + WHERE := range_cond(key1_1) AND + range_cond(key2_1) AND + other_cond AND + index_merge_OR_cond1(key3_1, key3_2, ...) + index_merge_OR_cond2(key4_1, key4_2, ...) + +where + + index_merge_OR_cond{N} := (range_cond(keyN_1) OR + range_cond(keyN_2) OR ...) + + + range_cond(keyX) := condition that allows to construct range access of keyX + and doesn't allow to construct range/index_merge accesses + for any keys of the table in question. + + +For such WHERE clauses, the range analyzer will produce SEL_TREE of this form: + + SEL_TREE( + range(key1_1), + ... + range(key2_1), + SEL_IMERGE( (1) + SEL_TREE(key3_1}) + SEL_TREE(key3_2}) + ... + ) + ... + ) + +which can be used to make a cost-based choice between range and index_merge. + +Limitations +----------- +This will not be a full solution in a sense that the range analyzer will not +be able to produce sel_tree (1) if the WHERE clause is specified in other form +(e.g. brackets were opened). + +TODO +---- +* is it a problem if there are keys that are referred to both from + index_merge and from range access? + +* How strict is the limitation on the form of the WHERE? + +* TODO: The optimizer didn't compare costs of index_merge and range before (ok + it did but that was done for accesses to different tables). Will there be any + possible gotchas here? DESCRIPTION: Current range optimizer will discard possible index_merge/[sort]union strategies when there is a possible range plan. This action is a part of measures we take to avoid combinatorial explosion of possible range/ index_merge strategies. A bad side effect of this is that for WHERE clauses in form t.key1= 'very-frequent-value' AND (t.key2='rare-value1' OR t.key3='rare-value2') the optimizer will - discard union(key2,key3) in favor of range(key1) - consider costs of using range(key1) and discard that plan also and the overall effect is that possible poor range access will cause possible good index_merge access not to be considered. This WL is to about lifting this limitation at least for some subset of WHERE clauses. HIGH-LEVEL SPECIFICATION: (Not a ready HLS but draft) <contents> Solution overview Limitations TODO </contents> Solution overview ================= The idea is to delay discarding potential index_merge plans until the point where it is really necessary. This way, we won't have to do much changes in the range analyzer, but will be able to keep potential index_merge plan just enough so that it's possible to take it into consideration together with range access plans. Since there are no changes in the optimizer, the ability to consider both range and index_merge options will be limited to WHERE clauses of this form: WHERE := range_cond(key1_1) AND range_cond(key2_1) AND other_cond AND index_merge_OR_cond1(key3_1, key3_2, ...) index_merge_OR_cond2(key4_1, key4_2, ...) where index_merge_OR_cond{N} := (range_cond(keyN_1) OR range_cond(keyN_2) OR ...) range_cond(keyX) := condition that allows to construct range access of keyX and doesn't allow to construct range/index_merge accesses for any keys of the table in question. For such WHERE clauses, the range analyzer will produce SEL_TREE of this form: SEL_TREE( range(key1_1), ... range(key2_1), SEL_IMERGE( (1) SEL_TREE(key3_1}) SEL_TREE(key3_2}) ... ) ... ) which can be used to make a cost-based choice between range and index_merge. Limitations ----------- This will not be a full solution in a sense that the range analyzer will not be able to produce sel_tree (1) if the WHERE clause is specified in other form (e.g. brackets were opened). TODO ---- * is it a problem if there are keys that are referred to both from index_merge and from range access? * How strict is the limitation on the form of the WHERE? * TODO: The optimizer didn't compare costs of index_merge and range before (ok it did but that was done for accesses to different tables). Will there be any possible gotchas here? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): index_merge optimizer: dont discard index_merge union strategies when range is available (24)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge optimizer: dont discard index_merge union strategies when range is available CREATION DATE..: Tue, 26 May 2009, 12:10 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Client-BackLog TASK ID........: 24 (http://askmonty.org/worklog/?tid=24) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Tue, 26 May 2009, 13:27)=-=- High-Level Specification modified. --- /tmp/wklog.24.old.305 2009-05-26 13:27:32.000000000 +0300 +++ /tmp/wklog.24.new.305 2009-05-26 13:27:32.000000000 +0300 @@ -1 +1,70 @@ +(Not a ready HLS but draft) +<contents> +Solution overview +Limitations +TODO + +</contents> + +Solution overview +================= +The idea is to delay discarding potential index_merge plans until the point +where it is really necessary. + +This way, we won't have to do much changes in the range analyzer, but will be +able to keep potential index_merge plan just enough so that it's possible to +take it into consideration together with range access plans. + +Since there are no changes in the optimizer, the ability to consider both +range and index_merge options will be limited to WHERE clauses of this form: + + WHERE := range_cond(key1_1) AND + range_cond(key2_1) AND + other_cond AND + index_merge_OR_cond1(key3_1, key3_2, ...) + index_merge_OR_cond2(key4_1, key4_2, ...) + +where + + index_merge_OR_cond{N} := (range_cond(keyN_1) OR + range_cond(keyN_2) OR ...) + + + range_cond(keyX) := condition that allows to construct range access of keyX + and doesn't allow to construct range/index_merge accesses + for any keys of the table in question. + + +For such WHERE clauses, the range analyzer will produce SEL_TREE of this form: + + SEL_TREE( + range(key1_1), + ... + range(key2_1), + SEL_IMERGE( (1) + SEL_TREE(key3_1}) + SEL_TREE(key3_2}) + ... + ) + ... + ) + +which can be used to make a cost-based choice between range and index_merge. + +Limitations +----------- +This will not be a full solution in a sense that the range analyzer will not +be able to produce sel_tree (1) if the WHERE clause is specified in other form +(e.g. brackets were opened). + +TODO +---- +* is it a problem if there are keys that are referred to both from + index_merge and from range access? + +* How strict is the limitation on the form of the WHERE? + +* TODO: The optimizer didn't compare costs of index_merge and range before (ok + it did but that was done for accesses to different tables). Will there be any + possible gotchas here? DESCRIPTION: Current range optimizer will discard possible index_merge/[sort]union strategies when there is a possible range plan. This action is a part of measures we take to avoid combinatorial explosion of possible range/ index_merge strategies. A bad side effect of this is that for WHERE clauses in form t.key1= 'very-frequent-value' AND (t.key2='rare-value1' OR t.key3='rare-value2') the optimizer will - discard union(key2,key3) in favor of range(key1) - consider costs of using range(key1) and discard that plan also and the overall effect is that possible poor range access will cause possible good index_merge access not to be considered. This WL is to about lifting this limitation at least for some subset of WHERE clauses. HIGH-LEVEL SPECIFICATION: (Not a ready HLS but draft) <contents> Solution overview Limitations TODO </contents> Solution overview ================= The idea is to delay discarding potential index_merge plans until the point where it is really necessary. This way, we won't have to do much changes in the range analyzer, but will be able to keep potential index_merge plan just enough so that it's possible to take it into consideration together with range access plans. Since there are no changes in the optimizer, the ability to consider both range and index_merge options will be limited to WHERE clauses of this form: WHERE := range_cond(key1_1) AND range_cond(key2_1) AND other_cond AND index_merge_OR_cond1(key3_1, key3_2, ...) index_merge_OR_cond2(key4_1, key4_2, ...) where index_merge_OR_cond{N} := (range_cond(keyN_1) OR range_cond(keyN_2) OR ...) range_cond(keyX) := condition that allows to construct range access of keyX and doesn't allow to construct range/index_merge accesses for any keys of the table in question. For such WHERE clauses, the range analyzer will produce SEL_TREE of this form: SEL_TREE( range(key1_1), ... range(key2_1), SEL_IMERGE( (1) SEL_TREE(key3_1}) SEL_TREE(key3_2}) ... ) ... ) which can be used to make a cost-based choice between range and index_merge. Limitations ----------- This will not be a full solution in a sense that the range analyzer will not be able to produce sel_tree (1) if the WHERE clause is specified in other form (e.g. brackets were opened). TODO ---- * is it a problem if there are keys that are referred to both from index_merge and from range access? * How strict is the limitation on the form of the WHERE? * TODO: The optimizer didn't compare costs of index_merge and range before (ok it did but that was done for accesses to different tables). Will there be any possible gotchas here? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): index_merge optimizer: dont discard index_merge union strategies when range is available (24)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge optimizer: dont discard index_merge union strategies when range is available CREATION DATE..: Tue, 26 May 2009, 12:10 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Client-BackLog TASK ID........: 24 (http://askmonty.org/worklog/?tid=24) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Current range optimizer will discard possible index_merge/[sort]union strategies when there is a possible range plan. This action is a part of measures we take to avoid combinatorial explosion of possible range/ index_merge strategies. A bad side effect of this is that for WHERE clauses in form t.key1= 'very-frequent-value' AND (t.key2='rare-value1' OR t.key3='rare-value2') the optimizer will - discard union(key2,key3) in favor of range(key1) - consider costs of using range(key1) and discard that plan also and the overall effect is that possible poor range access will cause possible good index_merge access not to be considered. This WL is to about lifting this limitation at least for some subset of WHERE clauses. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): index_merge optimizer: dont discard index_merge union strategies when range is available (24)
by worklog-noreply＠askmonty.org 26 May '09

26 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: index_merge optimizer: dont discard index_merge union strategies when range is available CREATION DATE..: Tue, 26 May 2009, 12:10 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Client-BackLog TASK ID........: 24 (http://askmonty.org/worklog/?tid=24) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Current range optimizer will discard possible index_merge/[sort]union strategies when there is a possible range plan. This action is a part of measures we take to avoid combinatorial explosion of possible range/ index_merge strategies. A bad side effect of this is that for WHERE clauses in form t.key1= 'very-frequent-value' AND (t.key2='rare-value1' OR t.key3='rare-value2') the optimizer will - discard union(key2,key3) in favor of range(key1) - consider costs of using range(key1) and discard that plan also and the overall effect is that possible poor range access will cause possible good index_merge access not to be considered. This WL is to about lifting this limitation at least for some subset of WHERE clauses. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2705: Fix ps_not_windows test for -all-static build which cannot load plugins.
by noreply＠launchpad.net 22 May '09

22 May '09

------------------------------------------------------------ revno: 2705 committer: knielsen(a)knielsen-hq.org branch nick: work-5.1-buildbot timestamp: Fri 2009-05-22 19:53:25 +0200 message: Fix ps_not_windows test for -all-static build which cannot load plugins. added: mysql-test/include/have_dynamic_loading.inc modified: mysql-test/t/ps_not_windows.test === added file 'mysql-test/include/have_dynamic_loading.inc' --- mysql-test/include/have_dynamic_loading.inc 1970-01-01 00:00:00 +0000 +++ mysql-test/include/have_dynamic_loading.inc 2009-05-22 17:53:25 +0000 @@ -0,0 +1,4 @@ +-- require r/have_dynamic_loading.require +disable_query_log; +show variables like 'have_dynamic_loading'; +enable_query_log; === modified file 'mysql-test/t/ps_not_windows.test' --- mysql-test/t/ps_not_windows.test 2007-01-02 23:31:12 +0000 +++ mysql-test/t/ps_not_windows.test 2009-05-22 17:53:25 +0000 @@ -2,6 +2,8 @@ --source include/not_embedded.inc # Non-windows specific ps tests. --source include/not_windows.inc +# Requires ability to install plugins. +--source include/have_dynamic_loading.inc # # Bug #20665: All commands supported in Stored Procedures should work in -- Main stable branch https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2705)
by knielsen＠knielsen-hq.org 22 May '09

22 May '09

#At lp:maria 2705 knielsen(a)knielsen-hq.org 2009-05-22 Fix ps_not_windows test for -all-static build which cannot load plugins. added: mysql-test/include/have_dynamic_loading.inc modified: mysql-test/t/ps_not_windows.test === added file 'mysql-test/include/have_dynamic_loading.inc' --- a/mysql-test/include/have_dynamic_loading.inc 1970-01-01 00:00:00 +0000 +++ b/mysql-test/include/have_dynamic_loading.inc 2009-05-22 17:53:25 +0000 @@ -0,0 +1,4 @@ +-- require r/have_dynamic_loading.require +disable_query_log; +show variables like 'have_dynamic_loading'; +enable_query_log; === modified file 'mysql-test/t/ps_not_windows.test' --- a/mysql-test/t/ps_not_windows.test 2007-01-02 23:31:12 +0000 +++ b/mysql-test/t/ps_not_windows.test 2009-05-22 17:53:25 +0000 @@ -2,6 +2,8 @@ --source include/not_embedded.inc # Non-windows specific ps tests. --source include/not_windows.inc +# Requires ability to install plugins. +--source include/have_dynamic_loading.inc # # Bug #20665: All commands supported in Stored Procedures should work in

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:51)=-=- Version updated. --- /tmp/wklog.20.old.31642 2009-05-22 17:51:21.000000000 +0300 +++ /tmp/wklog.20.new.31642 2009-05-22 17:51:21.000000000 +0300 @@ -1 +1 @@ -Server-9.x +Server-5.2 -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Category updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Status updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:51)=-=- Version updated. --- /tmp/wklog.20.old.31642 2009-05-22 17:51:21.000000000 +0300 +++ /tmp/wklog.20.new.31642 2009-05-22 17:51:21.000000000 +0300 @@ -1 +1 @@ -Server-9.x +Server-5.2 -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Category updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Status updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): Table Elimination: remove the facts table (20)
by worklog-noreply＠askmonty.org 22 May '09

22 May '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Table Elimination: remove the facts table CREATION DATE..: Thu, 21 May 2009, 01:07 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Psergey CATEGORY.......: Server-BackLog TASK ID........: 20 (http://askmonty.org/worklog/?tid=20) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Category updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Server-RawIdeaBin +Server-BackLog -=-=(Guest - Fri, 22 May 2009, 17:50)=-=- Status updated. --- /tmp/wklog.20.old.31598 2009-05-22 17:50:49.000000000 +0300 +++ /tmp/wklog.20.new.31598 2009-05-22 17:50:49.000000000 +0300 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Guest - Fri, 22 May 2009, 17:29)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.31038 2009-05-22 17:29:58.000000000 +0300 +++ /tmp/wklog.20.new.31038 2009-05-22 17:29:58.000000000 +0300 @@ -68,7 +68,7 @@ where ACBDT_Birthdate < '1950-01-01' -At this point, it's possible to conclude that table anchor can be removed from +At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( @@ -80,6 +80,18 @@ mean actors that have NULL/no names), but we're not interested in those as it is an inner join. +The result is (notice the changed select): + +select + actor_birthday.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor_birthdate + left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + How to remove ------------- * Find inner joins, such that -=-=(Guest - Thu, 21 May 2009, 22:54)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.28489 2009-05-21 22:54:52.000000000 +0300 +++ /tmp/wklog.20.new.28489 2009-05-21 22:54:52.000000000 +0300 @@ -11,7 +11,7 @@ -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( - AC_ID primary key, + AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), @@ -21,7 +21,7 @@ -- Another physical attribute: actor birthdate -- create table actor_birthdate ( - AC_ID primary key, + AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) -=-=(Psergey - Thu, 21 May 2009, 01:07)=-=- High-Level Specification modified. --- /tmp/wklog.20.old.26317 2009-05-21 01:07:55.000000000 +0300 +++ /tmp/wklog.20.new.26317 2009-05-21 01:07:55.000000000 +0300 @@ -1 +1,91 @@ +Consider the following schema: + +-- +-- The physical anchor table +-- +create table actor ( + AC_ID int primary key +); + +-- +-- A physical attribute: actor name (non-historized for simplicity) +-- +create table actor_name ( + AC_ID primary key, + ACNAM_Name varchar(20), + index(ACNAM_Name), + foreign key (AC_ID) references actor(AC_ID), +); + +-- +-- Another physical attribute: actor birthdate +-- +create table actor_birthdate ( + AC_ID primary key, + ACBDT_Birthdate datetime, + index(ACBDT_Birthdate), + foreign key (AC_ID) references actor(AC_ID) +); + +-- +-- The knot view: actor with its attributes +-- +create view actor_attributes as +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate + ... (other attributes follow) +from + actor + left join actor_name on (actor_name.AC_ID = actor.AC_ID) + left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + ... other attributes follow ... +; + +-- The query: names of senior actors +select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' + +Query processing +---------------- +The following steps will occur +* The VIEW will be resolved with algorithm=merge +* Outer-to-inner join conversion will make use of the + ACBDT_Birthdate < '1950-01-01' condition and change one outer join into + inner. +* Table elimination variant#1 will remove all unused attributes. + +And we'll end up with: + +select + actor.AC_ID, + actor_name.ACNAM_Name, + actor_birthdate.ACBDT_Birthdate +from + actor + join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) + left join actor_name on (actor_name.AC_ID = actor.AC_ID) +where + ACBDT_Birthdate < '1950-01-01' + +At this point, it's possible to conclude that table anchor can be removed from +the query plan: anchor model specifies that actor_name has a foreign key: + + create table actor_name ( + ... + foreign key (AC_ID) references actor(AC_ID), + +This means that each record in actor_name has exactly one match in table actor. +There may be records in `actor` that have no matches in `actor_name` (they +mean actors that have NULL/no names), but we're not interested in those as it +is an inner join. + +How to remove +------------- +* Find inner joins, such that + - some table T can be accessed using eq_ref access method. + - some other table T2 has Foreign Key that refers to table T. + - there are no references to table T anywhere in the query, except for + references to T.primary_key, which can be substituted for columns of T2. + DESCRIPTION: In certain cases it is possible to eliminate table when one is running inner joins and there is a foreign key relationship between the tables. HIGH-LEVEL SPECIFICATION: Consider the following schema: -- -- The physical anchor table -- create table actor ( AC_ID int primary key ); -- -- A physical attribute: actor name (non-historized for simplicity) -- create table actor_name ( AC_ID int primary key, ACNAM_Name varchar(20), index(ACNAM_Name), foreign key (AC_ID) references actor(AC_ID), ); -- -- Another physical attribute: actor birthdate -- create table actor_birthdate ( AC_ID int primary key, ACBDT_Birthdate datetime, index(ACBDT_Birthdate), foreign key (AC_ID) references actor(AC_ID) ); -- -- The knot view: actor with its attributes -- create view actor_attributes as select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate ... (other attributes follow) from actor left join actor_name on (actor_name.AC_ID = actor.AC_ID) left join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) ... other attributes follow ... ; -- The query: names of senior actors select ACNAM_Name from actor_attributes where ACBDT_Birthdate < '1950-01-01' Query processing ---------------- The following steps will occur * The VIEW will be resolved with algorithm=merge * Outer-to-inner join conversion will make use of the ACBDT_Birthdate < '1950-01-01' condition and change one outer join into inner. * Table elimination variant#1 will remove all unused attributes. And we'll end up with: select actor.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor join actor_birthdate on (actor_birthdate.AC_ID = actor.AC_ID) left join actor_name on (actor_name.AC_ID = actor.AC_ID) where ACBDT_Birthdate < '1950-01-01' At this point, it's possible to conclude that table actor can be removed from the query plan: anchor model specifies that actor_name has a foreign key: create table actor_name ( ... foreign key (AC_ID) references actor(AC_ID), This means that each record in actor_name has exactly one match in table actor. There may be records in `actor` that have no matches in `actor_name` (they mean actors that have NULL/no names), but we're not interested in those as it is an inner join. The result is (notice the changed select): select actor_birthday.AC_ID, actor_name.ACNAM_Name, actor_birthdate.ACBDT_Birthdate from actor_birthdate left join actor_name on (actor_name.AC_ID = actor_birthday.AC_ID) where ACBDT_Birthdate < '1950-01-01' How to remove ------------- * Find inner joins, such that - some table T can be accessed using eq_ref access method. - some other table T2 has Foreign Key that refers to table T. - there are no references to table T anywhere in the query, except for references to T.primary_key, which can be substituted for columns of T2. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0