- developers - lists.mariadb.org

Re: [Maria-developers] MariaDB Release Numbering
by Kristian Nielsen 25 Mar '10

25 Mar '10

Colin Charles <colin(a)askmonty.org> writes: > On 25 Mar 2010, at 02:09, Daniel Bartholomew wrote: > >> If so, my thinking is that the first 5.2 release will be called >> "5.2.1" >> and then go up from there. > > > This is the only logical way forward > > We can then say "5.2.1 branched from MySQL 5.x" (for example) > > We've got to be clear where we've pulled things from, because some > things might be fixed in later versions of MySQL. Also in case there > are changes (i.e. that may affect folk upgrading), its really > important to know where things are branched from > > Its also good that we "deviate" from their numbering. Putting on a > marketing hat, it does sound like we're doing well with a greater > version number (ok, I don't necessarily believe this, but I was semi- > convinced when I heard the explanation given to me by the marketing > folk at MySQL - it helps CIOs think, maybe) I don't want a marketing hat :) But from a technical point of view I just want to make it clear that there is no difference in this respect between MariaDB 5.1 and MariaDB 5.2. The only meaningful difference between 5.1 and 5.2 is that at some point we stopped adding features to 5.1 to make a stable release, and thus all additions that are not bugfixes are now called 5.2. But in terms of numbering from 1,2,3 or from corresponding MySQL versions, there is no difference. Any argument for one or the other numbering applies equally to both 5.1 and 5.2. The merging from MySQL is identical. So the consistent way would be to release 5.2.44, 5.2.45, ... (On the other hand there may be other reasons to prefer 5.2.1, 5.2.2, fine with me. We can't go back to MariaDB 5.1.1, but for MariaDB 5.2 we can choose). - Kristian.

2 3

[Maria-developers] bzr commit into Mariadb 5.2, with Maria 2.0:maria/5.2 branch (monty:2751)
by Michael Widenius 25 Mar '10

25 Mar '10

#At lp:maria/5.2 based on revid:sergii@pisem.net-20100323092233-t2gwaclx94hd6exa 2751 Michael Widenius 2010-03-25 simple speed & space optimization: - Avoid full inline of mark_trx_read_write() for many functions - Avoid somewhat expensive tests for every write/update/delete row modified: sql/handler.cc sql/handler.h sql/sql_base.cc per-file messages: sql/handler.h Adde ha_start_of_new_statement() to reset internal variables as part of the code in "open_table" that resets TABLE object for the new statement Faster mark_trx_read_write_part() sql/sql_base.cc Don't manipulate table->file internal structs directly === modified file 'sql/handler.cc' --- a/sql/handler.cc 2010-03-15 11:51:23 +0000 +++ b/sql/handler.cc 2010-03-25 13:33:39 +0000 @@ -3110,11 +3110,14 @@ int handler::ha_check(THD *thd, HA_CHECK if it is started. */ -inline void -handler::mark_trx_read_write() +handler::mark_trx_read_write_part2() { Ha_trx_info *ha_info= &ha_thd()->ha_data[ht->slot].ha_info[0]; + + /* Don't call this function again for this statement */ + mark_trx_done= TRUE; + /* When a storage engine method is called, the transaction must have been started, unless it's a DDL call, for which the === modified file 'sql/handler.h' --- a/sql/handler.h 2010-02-01 06:14:12 +0000 +++ b/sql/handler.h 2010-03-25 13:33:39 +0000 @@ -1134,6 +1134,7 @@ public: enum {NONE=0, INDEX, RND} inited; bool locked; bool implicit_emptied; /* Can be !=0 only if HEAP */ + bool mark_trx_done; const COND *pushed_cond; /** next_insert_id is the next value which should be inserted into the @@ -1177,7 +1178,7 @@ public: ref(0), key_used_on_scan(MAX_KEY), active_index(MAX_KEY), ref_length(sizeof(my_off_t)), ft_handler(0), inited(NONE), - locked(FALSE), implicit_emptied(0), + locked(FALSE), implicit_emptied(FALSE), mark_trx_done(FALSE), pushed_cond(0), next_insert_id(0), insert_id_for_cur_row(0), auto_inc_intervals_count(0) { @@ -1232,6 +1233,13 @@ public: DBUG_RETURN(rnd_end()); } int ha_reset(); + /* Tell handler (not storage engine) this is start of a new statement */ + void ha_start_of_new_statement() + { + ft_handler= 0; + mark_trx_done= FALSE; + } + /* this is necessary in many places, e.g. in HANDLER command */ int ha_index_or_rnd_end() { @@ -1943,8 +1951,13 @@ protected: private: /* Private helpers */ - inline void mark_trx_read_write(); -private: + void mark_trx_read_write_part2(); + inline void mark_trx_read_write() + { + if (!mark_trx_done) + mark_trx_read_write_part2(); + } + /* Low-level primitives for storage engines. These should be overridden by the storage engine class. To call these methods, use === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-03-15 11:51:23 +0000 +++ b/sql/sql_base.cc 2010-03-25 13:33:39 +0000 @@ -2996,7 +2996,7 @@ TABLE *open_table(THD *thd, TABLE_LIST * table->status=STATUS_NO_RECORD; table->insert_values= 0; table->fulltext_searched= 0; - table->file->ft_handler= 0; + table->file->ha_start_of_new_statement(); table->reginfo.impossible_range= 0; /* Catch wrong handling of the auto_increment_field_not_null. */ DBUG_ASSERT(!table->auto_increment_field_not_null);

1 0

[Maria-developers] Rev 28: Added note about noop IO scheduler for Linux. Refactor variable name. in file:///Users/hakan/work/monty_program/mariadb-tools/
by Hakan Kuecuekyilmaz 25 Mar '10

25 Mar '10

At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 28 revision-id: hakan(a)askmonty.org-20100325014205-kwsruwixwlymz1ti parent: hakan(a)askmonty.org-20100310010046-hwv56n4wfn4t4odp committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> branch nick: mariadb-tools timestamp: Thu 2010-03-25 02:42:05 +0100 message: Added note about noop IO scheduler for Linux. Refactor variable name. For MyISAM related tests added key_cache statistics dump out. === modified file 'sysbench/run-sysbench-myisam.sh' --- a/sysbench/run-sysbench-myisam.sh 2010-03-10 01:00:46 +0000 +++ b/sysbench/run-sysbench-myisam.sh 2010-03-25 01:42:05 +0000 @@ -6,16 +6,20 @@ # * Do not run this script with root privileges. We use # killall -9, which can cause severe side effects! # * By bzr pull we mean bzr merge --pull +# * For reasonable performance set your IO scheduler to noop or deadline, for +# reference please check +# http://www.mysqlperformanceblog.com/2009/01/30/linux-schedulers-in-tpcc-lik… # # Index sizes for 20 mio rows (--table-size=20000000). -# * delete.lua: 313M sbtest.MYI -# * insert.lua: 4.0K sbtest.MYI -# * oltp_complex_ro.lua: 313M sbtest.MYI -# * oltp_complex_rw.lua: 313M sbtest.MYI -# * oltp_simple.lua: 325M sbtest.MYI -# * select.lua: 313M sbtest.MYI -# * update_index.lua: 313M sbtest.MYI -# * update_non_index.lua: 313M sbtest.MYI +# * delete.lua 313M sbtest.MYI +# * insert.lua 4.0K sbtest.MYI +# * oltp_complex_ro.lua 313M sbtest.MYI +# * oltp_complex_rw.lua 313M sbtest.MYI +# * oltp_simple.lua 325M sbtest.MYI +# * select.lua 313M sbtest.MYI +# * select_random_ranges.lua 313M sbtest.MYI +# * update_index.lua 313M sbtest.MYI +# * update_non_index.lua 313M sbtest.MYI # # Hakan Kuecuekyilmaz <hakan at askmonty dot org> 2010-02-19. # @@ -60,13 +64,14 @@ # change these, except you exactly know what you are doing. # MYSQLADMIN='client/mysqladmin' +MYSQL='client/mysql' # # Variables. # MY_SOCKET="/tmp/mysql.sock" MYSQLADMIN_OPTIONS="--no-defaults -uroot --socket=$MY_SOCKET" -MYSQL_OPTIONS="--no-defaults \ +MYSQLD_OPTIONS="--no-defaults \ --datadir=$DATA_DIR \ --language=./sql/share/english \ --key_buffer_size=32M \ @@ -104,6 +109,7 @@ oltp_complex_rw.lua \ oltp_simple.lua \ select.lua \ + select_random_ranges.lua \ update_index.lua \ update_non_index.lua" @@ -240,7 +246,7 @@ } function start_mysqld { - sql/mysqld $MYSQL_OPTIONS & + sql/mysqld $MYSQLD_OPTIONS & j=0 STARTED=-1 @@ -269,7 +275,7 @@ # # Write out configurations used for future refernce. # -echo $MYSQL_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt +echo $MYSQLD_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt echo $SYSBENCH_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo '' >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo "Warm up time is: $WARM_UP_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt @@ -331,6 +337,7 @@ echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting warm up of $WARM_UP_TIME seconds." $SYSBENCH $SYSBENCH_OPTIONS_WARM_UP run sync + echo 'FLUSH STATUS' | $MYSQL -uroot echo "[$(date "+%Y-%m-%d %H:%M:%S")] Finnished warm up." echo "[$(date "+%Y-%m-%d %H:%M:%S")] Starting actual sysbench run." @@ -338,6 +345,8 @@ grep "write requests:" ${THIS_RESULT_DIR}/result${k}.txt | awk '{ print $4 }' | sed -e 's/(//' >> ${THIS_RESULT_DIR}/results.txt + echo 'SELECT * FROM INFORMATION_SCHEMA.KEY_CACHES' | $MYSQL -uroot > ${THIS_RESULT_DIR}/key_cache_stats{k}.txt + k=$(($k + 1)) done === modified file 'sysbench/run-sysbench.sh' --- a/sysbench/run-sysbench.sh 2010-03-10 00:02:01 +0000 +++ b/sysbench/run-sysbench.sh 2010-03-25 01:42:05 +0000 @@ -6,6 +6,9 @@ # * Do not run this script with root privileges. We use # killall -9, which can cause severe side effects! # * By bzr pull we mean bzr merge --pull +# * For reasonable performance set your IO scheduler to noop or deadline, for +# reference please check +# http://www.mysqlperformanceblog.com/2009/01/30/linux-schedulers-in-tpcc-lik… # # Hakan Kuecuekyilmaz <hakan at askmonty dot org> 2010-02-19. # @@ -56,7 +59,7 @@ # MY_SOCKET="/tmp/mysql.sock" MYSQLADMIN_OPTIONS="--no-defaults -uroot --socket=$MY_SOCKET" -MYSQL_OPTIONS="--no-defaults \ +MYSQLD_OPTIONS="--no-defaults \ --datadir=$DATA_DIR \ --language=./sql/share/english \ --max_connections=256 \ @@ -236,7 +239,7 @@ } function start_mysqld { - sql/mysqld $MYSQL_OPTIONS & + sql/mysqld $MYSQLD_OPTIONS & j=0 STARTED=-1 @@ -265,7 +268,7 @@ # # Write out configurations used for future refernce. # -echo $MYSQL_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt +echo $MYSQLD_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/mysqld_options.txt echo $SYSBENCH_OPTIONS > ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo '' >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt echo "Warm up time is: $WARM_UP_TIME" >> ${RESULT_DIR}/${TODAY}/${PRODUCT}/sysbench_options.txt

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2833: two crashes in the TC_LOG_MMAP:
by noreply＠launchpad.net 24 Mar '10

24 Mar '10

------------------------------------------------------------ revno: 2833 fixes bug(s): https://launchpad.net/bugs/544173 committer: Sergei Golubchik <sergii(a)pisem.net> branch nick: maria-5.1 timestamp: Wed 2010-03-24 23:12:39 +0100 message: two crashes in the TC_LOG_MMAP: 1. don't forget to initialize page->ptr 2. don't signal active->cond, if active is NULL added: mysql-test/suite/pbxt/r/pbxt_xa.result mysql-test/suite/pbxt/t/pbxt_xa.test modified: sql/log.cc -- lp:maria 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] Updated (by Psergey): Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE (90)
by worklog-noreply＠askmonty.org 24 Mar '10

24 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE CREATION DATE..: Sun, 28 Feb 2010, 13:45 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 90 (http://askmonty.org/worklog/?tid=90) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: -1 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Wed, 24 Mar 2010, 14:42)=-=- Low Level Design modified. --- /tmp/wklog.90.old.19182 2010-03-24 14:42:54.000000000 +0000 +++ /tmp/wklog.90.new.19182 2010-03-24 14:42:54.000000000 +0000 @@ -1 +1,140 @@ +<contents> +1. Applicability check +2. Representation +2.1 Option #1: Convert to TABLE_LIST +2.2 On subquery predicate removal +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 What is expected of the result of conversion +3. Pre-optimization steps +3.1 Constant detection +3.3 update_ref_and_keys +3.4 JOIN_TAB sorting criteria +4. Optimization +5. Execution +User interface. +</contents> + +We'll call the new execution strategy "jtbm-materialization", for the lack of +better name. + +1. Applicability check +====================== +The criteria for checking whether a subquery can be processed with +jtbm-materialization can be checked at JOIN::prepare stage (like it +happens with semi-join check) + +2. Representation +================= + +2.1 Option #1: Convert to TABLE_LIST +------------------------------------ +Make it work like semi-join nests: each jtbm-predicate is converted into a +TABLE_LIST object. This will make it + + - uniform with semi-joins (we've stepped on all rakes there) + - allow to process JTBM-subqueries in ON expressions + +simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base +tables. + +for EXPLAIN EXTENDED, it would be natural to print something semi-join like, +i.e. for + + SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) + +we'll print + + SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + +the XX part is not clear. we don't want to print 'ie' the second time here? + +2.2 On subquery predicate removal +--------------------------------- +Q: if we remove the subquery predicate permanently, who will run +fix_fields() for it? For semi-joins we don't have the problem as we +inject into ON expression (right? or not? we have sj_on_expr, too... +(Investigation: we the the same Item* pointer both in WHERE and +as sj_on_expr. fix_fields() is called for the WHERE part and that's +how sj_on_expr gets fixed. This works as long as +Item_func_eq::fix_fields() does not try to substitute itself with +another item). +A: ? + +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +------------------------------------------------------------ +JOIN_TABs only live for the duration of one PS re-execution, so we'll have to +- make conversion fully undoable +- perform it sufficiently late in the optimization process, at the point + where JOIN_TABs are already allocated. + +Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then +it will be impossible to handle JTBM queries inside/outside of outer joins. + +2.3 What is expected of the result of conversion +------------------------------------------------ +Join [pre]optimization relies on each optimized entity to have a bit in +table_map. + +TODO: where do we check if there will be enough bits for everyone? (at the + point where we assign them?) + +The bit stored in join_tab->table->map, and the apparent problem is that JTBM +join_tabs do not naturally have TABLE* object. + +We could use the the one that will be used for Materialization, but that will +stop working when we will have to include IN->EXISTS in the choice. + +Current approach: don't create a table. create a table_map element in JOIN_TAB +instead. Evgen has probably done something like that already. + +3. Pre-optimization steps +========================= +JOIN_TABs are allocated in make_join_statistics(). This where the changes will +be needed: for JOIN_TABs that correspond to JTBM-tables: + +- don't set tab->table, set tab->jtbm_select (or whatever) +- run subquery's optimizer to get its output cardinality + +3.1 Constant detection +---------------------- +What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate. + something IN (SELECT from_constant_join) -> is constant + +Do we need to mark their JOIN_TABs as constant? + +3.3 update_ref_and_keys +----------------------- +* Walk through JTBM elements and inject KEYUSE elements for their + IN-equalities. + +TODO: KEYUSE elements imply presense of KEYs! Which we don't have! + +3.4 JOIN_TAB sorting criteria +----------------------------- +Q: Where do we put JTBM's join_tab when pre-sorting records? +A: it should sort as regular table. + +TODO: where do we remove the predicates from the WHERE? + - remove them like SJ-converter does + - remove them with optimizer (like remove_eq_conds does) + +4. Optimization +=============== +Add a branch in best_access_path to account for +- JTBM-Materialization +- JTBM-Materialization-Scan. + +5. Execution +============ +* We should be able to reuse item_subselect.cc code for lookups +* But will have to use our own temptable scan code + +TODO: is it possible to have any unification with SJ-Materialization? + +User interface +-------------- +Any @@optimizer_switch flags for all this? + -=-=(Igor - Wed, 10 Mar 2010, 22:02)=-=- High Level Description modified. --- /tmp/wklog.90.old.2007 2010-03-10 22:02:23.000000000 +0000 +++ /tmp/wklog.90.new.2007 2010-03-10 22:02:23.000000000 +0000 @@ -13,8 +13,8 @@ for each record R2 in big_table such that oe=R1 pass R2 to output -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. +Semi-join materialization supports the inside-out strategy. This WL entry is +about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between -=-=(Igor - Wed, 10 Mar 2010, 21:52)=-=- Status updated. --- /tmp/wklog.90.old.882 2010-03-10 21:52:02.000000000 +0000 +++ /tmp/wklog.90.new.882 2010-03-10 21:52:02.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 15:37)=-=- High Level Description modified. --- /tmp/wklog.90.old.23524 2010-02-28 15:37:47.000000000 +0000 +++ /tmp/wklog.90.new.23524 2010-02-28 15:37:47.000000000 +0000 @@ -15,3 +15,7 @@ Semi-join materialization supports such strategy with SJM-Scan strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. + + +Once WL#89 is done, there will be a cost-based choice between +Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. -=-=(Psergey - Sun, 28 Feb 2010, 15:22)=-=- High-Level Specification modified. --- /tmp/wklog.90.old.23033 2010-02-28 15:22:09.000000000 +0000 +++ /tmp/wklog.90.new.23033 2010-02-28 15:22:09.000000000 +0000 @@ -1 +1,33 @@ +Basic idea on how this could be achieved: + +Pre-optimization phase +---------------------- + +The rewrite +~~~~~~~~~~~ +If we find a subquery predicate that is +- not processed by current semi-join optimizations +- is an AND-part of the WHERE/ON clause +- can be executed with Materialization + +then +- Remove the predicate from WHERE/ON clause +- Add a special JOIN_TAB object instead. + +Plan options +~~~~~~~~~~~~ +- Use the IN-equality to create KEYUSE elements. + +Optimization +------------ +- Pre-optimize the subquery so we know materialization cost +- Whenever best_access_path() encounters the "special JOIN_TAB" it should + consider two strategies: + A. Materialization and making lookups in the materialized table (if applicable) + B. Materialization and then scanning the materialized table. + + +EXPLAIN +------- +TODO how this will look in EXPLAIN output? -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21903 2010-02-28 14:47:54.000000000 +0000 +++ /tmp/wklog.90.new.21903 2010-02-28 14:47:54.000000000 +0000 @@ -1 +1 @@ - Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- High Level Description modified. --- /tmp/wklog.90.old.21880 2010-02-28 14:47:28.000000000 +0000 +++ /tmp/wklog.90.new.21880 2010-02-28 14:47:28.000000000 +0000 @@ -1,10 +1,17 @@ -For uncorrelated IN subqueries that can't be converted to semi-joins it is -necessary to make a cost-based choice between IN->EXISTS and Materialization -strategies. +Consider the following case: -Both strategies handle two cases: -1. A simple case w/o NULLs handling -2. Handling NULLs. +SELECT * FROM big_table +WHERE oe IN (SELECT ie FROM table_with_few_groups + WHERE ... + GROUP BY group_col) AND ... -This WL is about making cost-based decision for #1. +Here the best way to execute the query is: + Materialize the subquery; + # now run the join: + for each record R1 in materialized table + for each record R2 in big_table such that oe=R1 + pass R2 to output + +Semi-join materialization supports such strategy with SJM-Scan strategy. This WL +entry is about adding support for such strategies for non-semijoin subqueries. -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21859 2010-02-28 14:47:02.000000000 +0000 +++ /tmp/wklog.90.new.21859 2010-02-28 14:47:02.000000000 +0000 @@ -1 +1 @@ -Subqueries: cost-based choice between Materialization and IN->EXISTS transformation + Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=90&nolimit=1 DESCRIPTION: Consider the following case: SELECT * FROM big_table WHERE oe IN (SELECT ie FROM table_with_few_groups WHERE ... GROUP BY group_col) AND ... Here the best way to execute the query is: Materialize the subquery; # now run the join: for each record R1 in materialized table for each record R2 in big_table such that oe=R1 pass R2 to output Semi-join materialization supports the inside-out strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. HIGH-LEVEL SPECIFICATION: Basic idea on how this could be achieved: Pre-optimization phase ---------------------- The rewrite ~~~~~~~~~~~ If we find a subquery predicate that is - not processed by current semi-join optimizations - is an AND-part of the WHERE/ON clause - can be executed with Materialization then - Remove the predicate from WHERE/ON clause - Add a special JOIN_TAB object instead. Plan options ~~~~~~~~~~~~ - Use the IN-equality to create KEYUSE elements. Optimization ------------ - Pre-optimize the subquery so we know materialization cost - Whenever best_access_path() encounters the "special JOIN_TAB" it should consider two strategies: A. Materialization and making lookups in the materialized table (if applicable) B. Materialization and then scanning the materialized table. EXPLAIN ------- TODO how this will look in EXPLAIN output? LOW-LEVEL DESIGN: <contents> 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST 2.2 On subquery predicate removal 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away 2.3 What is expected of the result of conversion 3. Pre-optimization steps 3.1 Constant detection 3.3 update_ref_and_keys 3.4 JOIN_TAB sorting criteria 4. Optimization 5. Execution User interface. </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of better name. 1. Applicability check ====================== The criteria for checking whether a subquery can be processed with jtbm-materialization can be checked at JOIN::prepare stage (like it happens with semi-join check) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ Make it work like semi-join nests: each jtbm-predicate is converted into a TABLE_LIST object. This will make it - uniform with semi-joins (we've stepped on all rakes there) - allow to process JTBM-subqueries in ON expressions simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. for EXPLAIN EXTENDED, it would be natural to print something semi-join like, i.e. for SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) we'll print SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX the XX part is not clear. we don't want to print 'ie' the second time here? 2.2 On subquery predicate removal --------------------------------- Q: if we remove the subquery predicate permanently, who will run fix_fields() for it? For semi-joins we don't have the problem as we inject into ON expression (right? or not? we have sj_on_expr, too... (Investigation: we the the same Item* pointer both in WHERE and as sj_on_expr. fix_fields() is called for the WHERE part and that's how sj_on_expr gets fixed. This works as long as Item_func_eq::fix_fields() does not try to substitute itself with another item). A: ? 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable - perform it sufficiently late in the optimization process, at the point where JOIN_TABs are already allocated. Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. 2.3 What is expected of the result of conversion ------------------------------------------------ Join [pre]optimization relies on each optimized entity to have a bit in table_map. TODO: where do we check if there will be enough bits for everyone? (at the point where we assign them?) The bit stored in join_tab->table->map, and the apparent problem is that JTBM join_tabs do not naturally have TABLE* object. We could use the the one that will be used for Materialization, but that will stop working when we will have to include IN->EXISTS in the choice. Current approach: don't create a table. create a table_map element in JOIN_TAB instead. Evgen has probably done something like that already. 3. Pre-optimization steps ========================= JOIN_TABs are allocated in make_join_statistics(). This where the changes will be needed: for JOIN_TABs that correspond to JTBM-tables: - don't set tab->table, set tab->jtbm_select (or whatever) - run subquery's optimizer to get its output cardinality 3.1 Constant detection ---------------------- What about subqueries that "are constant"? const_item IN (SELECT uncorrelated) -> is constant, but not something we would want to evaluate. something IN (SELECT from_constant_join) -> is constant Do we need to mark their JOIN_TABs as constant? 3.3 update_ref_and_keys ----------------------- * Walk through JTBM elements and inject KEYUSE elements for their IN-equalities. TODO: KEYUSE elements imply presense of KEYs! Which we don't have! 3.4 JOIN_TAB sorting criteria ----------------------------- Q: Where do we put JTBM's join_tab when pre-sorting records? A: it should sort as regular table. TODO: where do we remove the predicates from the WHERE? - remove them like SJ-converter does - remove them with optimizer (like remove_eq_conds does) 4. Optimization =============== Add a branch in best_access_path to account for - JTBM-Materialization - JTBM-Materialization-Scan. 5. Execution ============ * We should be able to reuse item_subselect.cc code for lookups * But will have to use our own temptable scan code TODO: is it possible to have any unification with SJ-Materialization? User interface -------------- Any @@optimizer_switch flags for all this? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE (90)
by worklog-noreply＠askmonty.org 24 Mar '10

24 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE CREATION DATE..: Sun, 28 Feb 2010, 13:45 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 90 (http://askmonty.org/worklog/?tid=90) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: -1 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Wed, 24 Mar 2010, 14:42)=-=- Low Level Design modified. --- /tmp/wklog.90.old.19182 2010-03-24 14:42:54.000000000 +0000 +++ /tmp/wklog.90.new.19182 2010-03-24 14:42:54.000000000 +0000 @@ -1 +1,140 @@ +<contents> +1. Applicability check +2. Representation +2.1 Option #1: Convert to TABLE_LIST +2.2 On subquery predicate removal +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 What is expected of the result of conversion +3. Pre-optimization steps +3.1 Constant detection +3.3 update_ref_and_keys +3.4 JOIN_TAB sorting criteria +4. Optimization +5. Execution +User interface. +</contents> + +We'll call the new execution strategy "jtbm-materialization", for the lack of +better name. + +1. Applicability check +====================== +The criteria for checking whether a subquery can be processed with +jtbm-materialization can be checked at JOIN::prepare stage (like it +happens with semi-join check) + +2. Representation +================= + +2.1 Option #1: Convert to TABLE_LIST +------------------------------------ +Make it work like semi-join nests: each jtbm-predicate is converted into a +TABLE_LIST object. This will make it + + - uniform with semi-joins (we've stepped on all rakes there) + - allow to process JTBM-subqueries in ON expressions + +simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base +tables. + +for EXPLAIN EXTENDED, it would be natural to print something semi-join like, +i.e. for + + SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) + +we'll print + + SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + +the XX part is not clear. we don't want to print 'ie' the second time here? + +2.2 On subquery predicate removal +--------------------------------- +Q: if we remove the subquery predicate permanently, who will run +fix_fields() for it? For semi-joins we don't have the problem as we +inject into ON expression (right? or not? we have sj_on_expr, too... +(Investigation: we the the same Item* pointer both in WHERE and +as sj_on_expr. fix_fields() is called for the WHERE part and that's +how sj_on_expr gets fixed. This works as long as +Item_func_eq::fix_fields() does not try to substitute itself with +another item). +A: ? + +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +------------------------------------------------------------ +JOIN_TABs only live for the duration of one PS re-execution, so we'll have to +- make conversion fully undoable +- perform it sufficiently late in the optimization process, at the point + where JOIN_TABs are already allocated. + +Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then +it will be impossible to handle JTBM queries inside/outside of outer joins. + +2.3 What is expected of the result of conversion +------------------------------------------------ +Join [pre]optimization relies on each optimized entity to have a bit in +table_map. + +TODO: where do we check if there will be enough bits for everyone? (at the + point where we assign them?) + +The bit stored in join_tab->table->map, and the apparent problem is that JTBM +join_tabs do not naturally have TABLE* object. + +We could use the the one that will be used for Materialization, but that will +stop working when we will have to include IN->EXISTS in the choice. + +Current approach: don't create a table. create a table_map element in JOIN_TAB +instead. Evgen has probably done something like that already. + +3. Pre-optimization steps +========================= +JOIN_TABs are allocated in make_join_statistics(). This where the changes will +be needed: for JOIN_TABs that correspond to JTBM-tables: + +- don't set tab->table, set tab->jtbm_select (or whatever) +- run subquery's optimizer to get its output cardinality + +3.1 Constant detection +---------------------- +What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate. + something IN (SELECT from_constant_join) -> is constant + +Do we need to mark their JOIN_TABs as constant? + +3.3 update_ref_and_keys +----------------------- +* Walk through JTBM elements and inject KEYUSE elements for their + IN-equalities. + +TODO: KEYUSE elements imply presense of KEYs! Which we don't have! + +3.4 JOIN_TAB sorting criteria +----------------------------- +Q: Where do we put JTBM's join_tab when pre-sorting records? +A: it should sort as regular table. + +TODO: where do we remove the predicates from the WHERE? + - remove them like SJ-converter does + - remove them with optimizer (like remove_eq_conds does) + +4. Optimization +=============== +Add a branch in best_access_path to account for +- JTBM-Materialization +- JTBM-Materialization-Scan. + +5. Execution +============ +* We should be able to reuse item_subselect.cc code for lookups +* But will have to use our own temptable scan code + +TODO: is it possible to have any unification with SJ-Materialization? + +User interface +-------------- +Any @@optimizer_switch flags for all this? + -=-=(Igor - Wed, 10 Mar 2010, 22:02)=-=- High Level Description modified. --- /tmp/wklog.90.old.2007 2010-03-10 22:02:23.000000000 +0000 +++ /tmp/wklog.90.new.2007 2010-03-10 22:02:23.000000000 +0000 @@ -13,8 +13,8 @@ for each record R2 in big_table such that oe=R1 pass R2 to output -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. +Semi-join materialization supports the inside-out strategy. This WL entry is +about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between -=-=(Igor - Wed, 10 Mar 2010, 21:52)=-=- Status updated. --- /tmp/wklog.90.old.882 2010-03-10 21:52:02.000000000 +0000 +++ /tmp/wklog.90.new.882 2010-03-10 21:52:02.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 15:37)=-=- High Level Description modified. --- /tmp/wklog.90.old.23524 2010-02-28 15:37:47.000000000 +0000 +++ /tmp/wklog.90.new.23524 2010-02-28 15:37:47.000000000 +0000 @@ -15,3 +15,7 @@ Semi-join materialization supports such strategy with SJM-Scan strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. + + +Once WL#89 is done, there will be a cost-based choice between +Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. -=-=(Psergey - Sun, 28 Feb 2010, 15:22)=-=- High-Level Specification modified. --- /tmp/wklog.90.old.23033 2010-02-28 15:22:09.000000000 +0000 +++ /tmp/wklog.90.new.23033 2010-02-28 15:22:09.000000000 +0000 @@ -1 +1,33 @@ +Basic idea on how this could be achieved: + +Pre-optimization phase +---------------------- + +The rewrite +~~~~~~~~~~~ +If we find a subquery predicate that is +- not processed by current semi-join optimizations +- is an AND-part of the WHERE/ON clause +- can be executed with Materialization + +then +- Remove the predicate from WHERE/ON clause +- Add a special JOIN_TAB object instead. + +Plan options +~~~~~~~~~~~~ +- Use the IN-equality to create KEYUSE elements. + +Optimization +------------ +- Pre-optimize the subquery so we know materialization cost +- Whenever best_access_path() encounters the "special JOIN_TAB" it should + consider two strategies: + A. Materialization and making lookups in the materialized table (if applicable) + B. Materialization and then scanning the materialized table. + + +EXPLAIN +------- +TODO how this will look in EXPLAIN output? -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21903 2010-02-28 14:47:54.000000000 +0000 +++ /tmp/wklog.90.new.21903 2010-02-28 14:47:54.000000000 +0000 @@ -1 +1 @@ - Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- High Level Description modified. --- /tmp/wklog.90.old.21880 2010-02-28 14:47:28.000000000 +0000 +++ /tmp/wklog.90.new.21880 2010-02-28 14:47:28.000000000 +0000 @@ -1,10 +1,17 @@ -For uncorrelated IN subqueries that can't be converted to semi-joins it is -necessary to make a cost-based choice between IN->EXISTS and Materialization -strategies. +Consider the following case: -Both strategies handle two cases: -1. A simple case w/o NULLs handling -2. Handling NULLs. +SELECT * FROM big_table +WHERE oe IN (SELECT ie FROM table_with_few_groups + WHERE ... + GROUP BY group_col) AND ... -This WL is about making cost-based decision for #1. +Here the best way to execute the query is: + Materialize the subquery; + # now run the join: + for each record R1 in materialized table + for each record R2 in big_table such that oe=R1 + pass R2 to output + +Semi-join materialization supports such strategy with SJM-Scan strategy. This WL +entry is about adding support for such strategies for non-semijoin subqueries. -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21859 2010-02-28 14:47:02.000000000 +0000 +++ /tmp/wklog.90.new.21859 2010-02-28 14:47:02.000000000 +0000 @@ -1 +1 @@ -Subqueries: cost-based choice between Materialization and IN->EXISTS transformation + Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=90&nolimit=1 DESCRIPTION: Consider the following case: SELECT * FROM big_table WHERE oe IN (SELECT ie FROM table_with_few_groups WHERE ... GROUP BY group_col) AND ... Here the best way to execute the query is: Materialize the subquery; # now run the join: for each record R1 in materialized table for each record R2 in big_table such that oe=R1 pass R2 to output Semi-join materialization supports the inside-out strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. HIGH-LEVEL SPECIFICATION: Basic idea on how this could be achieved: Pre-optimization phase ---------------------- The rewrite ~~~~~~~~~~~ If we find a subquery predicate that is - not processed by current semi-join optimizations - is an AND-part of the WHERE/ON clause - can be executed with Materialization then - Remove the predicate from WHERE/ON clause - Add a special JOIN_TAB object instead. Plan options ~~~~~~~~~~~~ - Use the IN-equality to create KEYUSE elements. Optimization ------------ - Pre-optimize the subquery so we know materialization cost - Whenever best_access_path() encounters the "special JOIN_TAB" it should consider two strategies: A. Materialization and making lookups in the materialized table (if applicable) B. Materialization and then scanning the materialized table. EXPLAIN ------- TODO how this will look in EXPLAIN output? LOW-LEVEL DESIGN: <contents> 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST 2.2 On subquery predicate removal 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away 2.3 What is expected of the result of conversion 3. Pre-optimization steps 3.1 Constant detection 3.3 update_ref_and_keys 3.4 JOIN_TAB sorting criteria 4. Optimization 5. Execution User interface. </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of better name. 1. Applicability check ====================== The criteria for checking whether a subquery can be processed with jtbm-materialization can be checked at JOIN::prepare stage (like it happens with semi-join check) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ Make it work like semi-join nests: each jtbm-predicate is converted into a TABLE_LIST object. This will make it - uniform with semi-joins (we've stepped on all rakes there) - allow to process JTBM-subqueries in ON expressions simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. for EXPLAIN EXTENDED, it would be natural to print something semi-join like, i.e. for SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) we'll print SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX the XX part is not clear. we don't want to print 'ie' the second time here? 2.2 On subquery predicate removal --------------------------------- Q: if we remove the subquery predicate permanently, who will run fix_fields() for it? For semi-joins we don't have the problem as we inject into ON expression (right? or not? we have sj_on_expr, too... (Investigation: we the the same Item* pointer both in WHERE and as sj_on_expr. fix_fields() is called for the WHERE part and that's how sj_on_expr gets fixed. This works as long as Item_func_eq::fix_fields() does not try to substitute itself with another item). A: ? 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable - perform it sufficiently late in the optimization process, at the point where JOIN_TABs are already allocated. Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. 2.3 What is expected of the result of conversion ------------------------------------------------ Join [pre]optimization relies on each optimized entity to have a bit in table_map. TODO: where do we check if there will be enough bits for everyone? (at the point where we assign them?) The bit stored in join_tab->table->map, and the apparent problem is that JTBM join_tabs do not naturally have TABLE* object. We could use the the one that will be used for Materialization, but that will stop working when we will have to include IN->EXISTS in the choice. Current approach: don't create a table. create a table_map element in JOIN_TAB instead. Evgen has probably done something like that already. 3. Pre-optimization steps ========================= JOIN_TABs are allocated in make_join_statistics(). This where the changes will be needed: for JOIN_TABs that correspond to JTBM-tables: - don't set tab->table, set tab->jtbm_select (or whatever) - run subquery's optimizer to get its output cardinality 3.1 Constant detection ---------------------- What about subqueries that "are constant"? const_item IN (SELECT uncorrelated) -> is constant, but not something we would want to evaluate. something IN (SELECT from_constant_join) -> is constant Do we need to mark their JOIN_TABs as constant? 3.3 update_ref_and_keys ----------------------- * Walk through JTBM elements and inject KEYUSE elements for their IN-equalities. TODO: KEYUSE elements imply presense of KEYs! Which we don't have! 3.4 JOIN_TAB sorting criteria ----------------------------- Q: Where do we put JTBM's join_tab when pre-sorting records? A: it should sort as regular table. TODO: where do we remove the predicates from the WHERE? - remove them like SJ-converter does - remove them with optimizer (like remove_eq_conds does) 4. Optimization =============== Add a branch in best_access_path to account for - JTBM-Materialization - JTBM-Materialization-Scan. 5. Execution ============ * We should be able to reuse item_subselect.cc code for lookups * But will have to use our own temptable scan code TODO: is it possible to have any unification with SJ-Materialization? User interface -------------- Any @@optimizer_switch flags for all this? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE (90)
by worklog-noreply＠askmonty.org 24 Mar '10

24 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE CREATION DATE..: Sun, 28 Feb 2010, 13:45 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 90 (http://askmonty.org/worklog/?tid=90) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: -1 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Wed, 24 Mar 2010, 14:42)=-=- Low Level Design modified. --- /tmp/wklog.90.old.19182 2010-03-24 14:42:54.000000000 +0000 +++ /tmp/wklog.90.new.19182 2010-03-24 14:42:54.000000000 +0000 @@ -1 +1,140 @@ +<contents> +1. Applicability check +2. Representation +2.1 Option #1: Convert to TABLE_LIST +2.2 On subquery predicate removal +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 What is expected of the result of conversion +3. Pre-optimization steps +3.1 Constant detection +3.3 update_ref_and_keys +3.4 JOIN_TAB sorting criteria +4. Optimization +5. Execution +User interface. +</contents> + +We'll call the new execution strategy "jtbm-materialization", for the lack of +better name. + +1. Applicability check +====================== +The criteria for checking whether a subquery can be processed with +jtbm-materialization can be checked at JOIN::prepare stage (like it +happens with semi-join check) + +2. Representation +================= + +2.1 Option #1: Convert to TABLE_LIST +------------------------------------ +Make it work like semi-join nests: each jtbm-predicate is converted into a +TABLE_LIST object. This will make it + + - uniform with semi-joins (we've stepped on all rakes there) + - allow to process JTBM-subqueries in ON expressions + +simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base +tables. + +for EXPLAIN EXTENDED, it would be natural to print something semi-join like, +i.e. for + + SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) + +we'll print + + SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + +the XX part is not clear. we don't want to print 'ie' the second time here? + +2.2 On subquery predicate removal +--------------------------------- +Q: if we remove the subquery predicate permanently, who will run +fix_fields() for it? For semi-joins we don't have the problem as we +inject into ON expression (right? or not? we have sj_on_expr, too... +(Investigation: we the the same Item* pointer both in WHERE and +as sj_on_expr. fix_fields() is called for the WHERE part and that's +how sj_on_expr gets fixed. This works as long as +Item_func_eq::fix_fields() does not try to substitute itself with +another item). +A: ? + +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +------------------------------------------------------------ +JOIN_TABs only live for the duration of one PS re-execution, so we'll have to +- make conversion fully undoable +- perform it sufficiently late in the optimization process, at the point + where JOIN_TABs are already allocated. + +Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then +it will be impossible to handle JTBM queries inside/outside of outer joins. + +2.3 What is expected of the result of conversion +------------------------------------------------ +Join [pre]optimization relies on each optimized entity to have a bit in +table_map. + +TODO: where do we check if there will be enough bits for everyone? (at the + point where we assign them?) + +The bit stored in join_tab->table->map, and the apparent problem is that JTBM +join_tabs do not naturally have TABLE* object. + +We could use the the one that will be used for Materialization, but that will +stop working when we will have to include IN->EXISTS in the choice. + +Current approach: don't create a table. create a table_map element in JOIN_TAB +instead. Evgen has probably done something like that already. + +3. Pre-optimization steps +========================= +JOIN_TABs are allocated in make_join_statistics(). This where the changes will +be needed: for JOIN_TABs that correspond to JTBM-tables: + +- don't set tab->table, set tab->jtbm_select (or whatever) +- run subquery's optimizer to get its output cardinality + +3.1 Constant detection +---------------------- +What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate. + something IN (SELECT from_constant_join) -> is constant + +Do we need to mark their JOIN_TABs as constant? + +3.3 update_ref_and_keys +----------------------- +* Walk through JTBM elements and inject KEYUSE elements for their + IN-equalities. + +TODO: KEYUSE elements imply presense of KEYs! Which we don't have! + +3.4 JOIN_TAB sorting criteria +----------------------------- +Q: Where do we put JTBM's join_tab when pre-sorting records? +A: it should sort as regular table. + +TODO: where do we remove the predicates from the WHERE? + - remove them like SJ-converter does + - remove them with optimizer (like remove_eq_conds does) + +4. Optimization +=============== +Add a branch in best_access_path to account for +- JTBM-Materialization +- JTBM-Materialization-Scan. + +5. Execution +============ +* We should be able to reuse item_subselect.cc code for lookups +* But will have to use our own temptable scan code + +TODO: is it possible to have any unification with SJ-Materialization? + +User interface +-------------- +Any @@optimizer_switch flags for all this? + -=-=(Igor - Wed, 10 Mar 2010, 22:02)=-=- High Level Description modified. --- /tmp/wklog.90.old.2007 2010-03-10 22:02:23.000000000 +0000 +++ /tmp/wklog.90.new.2007 2010-03-10 22:02:23.000000000 +0000 @@ -13,8 +13,8 @@ for each record R2 in big_table such that oe=R1 pass R2 to output -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. +Semi-join materialization supports the inside-out strategy. This WL entry is +about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between -=-=(Igor - Wed, 10 Mar 2010, 21:52)=-=- Status updated. --- /tmp/wklog.90.old.882 2010-03-10 21:52:02.000000000 +0000 +++ /tmp/wklog.90.new.882 2010-03-10 21:52:02.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 15:37)=-=- High Level Description modified. --- /tmp/wklog.90.old.23524 2010-02-28 15:37:47.000000000 +0000 +++ /tmp/wklog.90.new.23524 2010-02-28 15:37:47.000000000 +0000 @@ -15,3 +15,7 @@ Semi-join materialization supports such strategy with SJM-Scan strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. + + +Once WL#89 is done, there will be a cost-based choice between +Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. -=-=(Psergey - Sun, 28 Feb 2010, 15:22)=-=- High-Level Specification modified. --- /tmp/wklog.90.old.23033 2010-02-28 15:22:09.000000000 +0000 +++ /tmp/wklog.90.new.23033 2010-02-28 15:22:09.000000000 +0000 @@ -1 +1,33 @@ +Basic idea on how this could be achieved: + +Pre-optimization phase +---------------------- + +The rewrite +~~~~~~~~~~~ +If we find a subquery predicate that is +- not processed by current semi-join optimizations +- is an AND-part of the WHERE/ON clause +- can be executed with Materialization + +then +- Remove the predicate from WHERE/ON clause +- Add a special JOIN_TAB object instead. + +Plan options +~~~~~~~~~~~~ +- Use the IN-equality to create KEYUSE elements. + +Optimization +------------ +- Pre-optimize the subquery so we know materialization cost +- Whenever best_access_path() encounters the "special JOIN_TAB" it should + consider two strategies: + A. Materialization and making lookups in the materialized table (if applicable) + B. Materialization and then scanning the materialized table. + + +EXPLAIN +------- +TODO how this will look in EXPLAIN output? -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21903 2010-02-28 14:47:54.000000000 +0000 +++ /tmp/wklog.90.new.21903 2010-02-28 14:47:54.000000000 +0000 @@ -1 +1 @@ - Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- High Level Description modified. --- /tmp/wklog.90.old.21880 2010-02-28 14:47:28.000000000 +0000 +++ /tmp/wklog.90.new.21880 2010-02-28 14:47:28.000000000 +0000 @@ -1,10 +1,17 @@ -For uncorrelated IN subqueries that can't be converted to semi-joins it is -necessary to make a cost-based choice between IN->EXISTS and Materialization -strategies. +Consider the following case: -Both strategies handle two cases: -1. A simple case w/o NULLs handling -2. Handling NULLs. +SELECT * FROM big_table +WHERE oe IN (SELECT ie FROM table_with_few_groups + WHERE ... + GROUP BY group_col) AND ... -This WL is about making cost-based decision for #1. +Here the best way to execute the query is: + Materialize the subquery; + # now run the join: + for each record R1 in materialized table + for each record R2 in big_table such that oe=R1 + pass R2 to output + +Semi-join materialization supports such strategy with SJM-Scan strategy. This WL +entry is about adding support for such strategies for non-semijoin subqueries. -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21859 2010-02-28 14:47:02.000000000 +0000 +++ /tmp/wklog.90.new.21859 2010-02-28 14:47:02.000000000 +0000 @@ -1 +1 @@ -Subqueries: cost-based choice between Materialization and IN->EXISTS transformation + Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=90&nolimit=1 DESCRIPTION: Consider the following case: SELECT * FROM big_table WHERE oe IN (SELECT ie FROM table_with_few_groups WHERE ... GROUP BY group_col) AND ... Here the best way to execute the query is: Materialize the subquery; # now run the join: for each record R1 in materialized table for each record R2 in big_table such that oe=R1 pass R2 to output Semi-join materialization supports the inside-out strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. HIGH-LEVEL SPECIFICATION: Basic idea on how this could be achieved: Pre-optimization phase ---------------------- The rewrite ~~~~~~~~~~~ If we find a subquery predicate that is - not processed by current semi-join optimizations - is an AND-part of the WHERE/ON clause - can be executed with Materialization then - Remove the predicate from WHERE/ON clause - Add a special JOIN_TAB object instead. Plan options ~~~~~~~~~~~~ - Use the IN-equality to create KEYUSE elements. Optimization ------------ - Pre-optimize the subquery so we know materialization cost - Whenever best_access_path() encounters the "special JOIN_TAB" it should consider two strategies: A. Materialization and making lookups in the materialized table (if applicable) B. Materialization and then scanning the materialized table. EXPLAIN ------- TODO how this will look in EXPLAIN output? LOW-LEVEL DESIGN: <contents> 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST 2.2 On subquery predicate removal 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away 2.3 What is expected of the result of conversion 3. Pre-optimization steps 3.1 Constant detection 3.3 update_ref_and_keys 3.4 JOIN_TAB sorting criteria 4. Optimization 5. Execution User interface. </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of better name. 1. Applicability check ====================== The criteria for checking whether a subquery can be processed with jtbm-materialization can be checked at JOIN::prepare stage (like it happens with semi-join check) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ Make it work like semi-join nests: each jtbm-predicate is converted into a TABLE_LIST object. This will make it - uniform with semi-joins (we've stepped on all rakes there) - allow to process JTBM-subqueries in ON expressions simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. for EXPLAIN EXTENDED, it would be natural to print something semi-join like, i.e. for SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) we'll print SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX the XX part is not clear. we don't want to print 'ie' the second time here? 2.2 On subquery predicate removal --------------------------------- Q: if we remove the subquery predicate permanently, who will run fix_fields() for it? For semi-joins we don't have the problem as we inject into ON expression (right? or not? we have sj_on_expr, too... (Investigation: we the the same Item* pointer both in WHERE and as sj_on_expr. fix_fields() is called for the WHERE part and that's how sj_on_expr gets fixed. This works as long as Item_func_eq::fix_fields() does not try to substitute itself with another item). A: ? 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable - perform it sufficiently late in the optimization process, at the point where JOIN_TABs are already allocated. Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. 2.3 What is expected of the result of conversion ------------------------------------------------ Join [pre]optimization relies on each optimized entity to have a bit in table_map. TODO: where do we check if there will be enough bits for everyone? (at the point where we assign them?) The bit stored in join_tab->table->map, and the apparent problem is that JTBM join_tabs do not naturally have TABLE* object. We could use the the one that will be used for Materialization, but that will stop working when we will have to include IN->EXISTS in the choice. Current approach: don't create a table. create a table_map element in JOIN_TAB instead. Evgen has probably done something like that already. 3. Pre-optimization steps ========================= JOIN_TABs are allocated in make_join_statistics(). This where the changes will be needed: for JOIN_TABs that correspond to JTBM-tables: - don't set tab->table, set tab->jtbm_select (or whatever) - run subquery's optimizer to get its output cardinality 3.1 Constant detection ---------------------- What about subqueries that "are constant"? const_item IN (SELECT uncorrelated) -> is constant, but not something we would want to evaluate. something IN (SELECT from_constant_join) -> is constant Do we need to mark their JOIN_TABs as constant? 3.3 update_ref_and_keys ----------------------- * Walk through JTBM elements and inject KEYUSE elements for their IN-equalities. TODO: KEYUSE elements imply presense of KEYs! Which we don't have! 3.4 JOIN_TAB sorting criteria ----------------------------- Q: Where do we put JTBM's join_tab when pre-sorting records? A: it should sort as regular table. TODO: where do we remove the predicates from the WHERE? - remove them like SJ-converter does - remove them with optimizer (like remove_eq_conds does) 4. Optimization =============== Add a branch in best_access_path to account for - JTBM-Materialization - JTBM-Materialization-Scan. 5. Execution ============ * We should be able to reuse item_subselect.cc code for lookups * But will have to use our own temptable scan code TODO: is it possible to have any unification with SJ-Materialization? User interface -------------- Any @@optimizer_switch flags for all this? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE (90)
by worklog-noreply＠askmonty.org 24 Mar '10

24 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE CREATION DATE..: Sun, 28 Feb 2010, 13:45 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: Igor, Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 90 (http://askmonty.org/worklog/?tid=90) VERSION........: Server-5.3 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: -1 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Wed, 24 Mar 2010, 14:42)=-=- Low Level Design modified. --- /tmp/wklog.90.old.19182 2010-03-24 14:42:54.000000000 +0000 +++ /tmp/wklog.90.new.19182 2010-03-24 14:42:54.000000000 +0000 @@ -1 +1,140 @@ +<contents> +1. Applicability check +2. Representation +2.1 Option #1: Convert to TABLE_LIST +2.2 On subquery predicate removal +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +2.3 What is expected of the result of conversion +3. Pre-optimization steps +3.1 Constant detection +3.3 update_ref_and_keys +3.4 JOIN_TAB sorting criteria +4. Optimization +5. Execution +User interface. +</contents> + +We'll call the new execution strategy "jtbm-materialization", for the lack of +better name. + +1. Applicability check +====================== +The criteria for checking whether a subquery can be processed with +jtbm-materialization can be checked at JOIN::prepare stage (like it +happens with semi-join check) + +2. Representation +================= + +2.1 Option #1: Convert to TABLE_LIST +------------------------------------ +Make it work like semi-join nests: each jtbm-predicate is converted into a +TABLE_LIST object. This will make it + + - uniform with semi-joins (we've stepped on all rakes there) + - allow to process JTBM-subqueries in ON expressions + +simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base +tables. + +for EXPLAIN EXTENDED, it would be natural to print something semi-join like, +i.e. for + + SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) + +we'll print + + SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX + +the XX part is not clear. we don't want to print 'ie' the second time here? + +2.2 On subquery predicate removal +--------------------------------- +Q: if we remove the subquery predicate permanently, who will run +fix_fields() for it? For semi-joins we don't have the problem as we +inject into ON expression (right? or not? we have sj_on_expr, too... +(Investigation: we the the same Item* pointer both in WHERE and +as sj_on_expr. fix_fields() is called for the WHERE part and that's +how sj_on_expr gets fixed. This works as long as +Item_func_eq::fix_fields() does not try to substitute itself with +another item). +A: ? + +2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away +------------------------------------------------------------ +JOIN_TABs only live for the duration of one PS re-execution, so we'll have to +- make conversion fully undoable +- perform it sufficiently late in the optimization process, at the point + where JOIN_TABs are already allocated. + +Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then +it will be impossible to handle JTBM queries inside/outside of outer joins. + +2.3 What is expected of the result of conversion +------------------------------------------------ +Join [pre]optimization relies on each optimized entity to have a bit in +table_map. + +TODO: where do we check if there will be enough bits for everyone? (at the + point where we assign them?) + +The bit stored in join_tab->table->map, and the apparent problem is that JTBM +join_tabs do not naturally have TABLE* object. + +We could use the the one that will be used for Materialization, but that will +stop working when we will have to include IN->EXISTS in the choice. + +Current approach: don't create a table. create a table_map element in JOIN_TAB +instead. Evgen has probably done something like that already. + +3. Pre-optimization steps +========================= +JOIN_TABs are allocated in make_join_statistics(). This where the changes will +be needed: for JOIN_TABs that correspond to JTBM-tables: + +- don't set tab->table, set tab->jtbm_select (or whatever) +- run subquery's optimizer to get its output cardinality + +3.1 Constant detection +---------------------- +What about subqueries that "are constant"? + const_item IN (SELECT uncorrelated) -> is constant, but not something + we would want to evaluate. + something IN (SELECT from_constant_join) -> is constant + +Do we need to mark their JOIN_TABs as constant? + +3.3 update_ref_and_keys +----------------------- +* Walk through JTBM elements and inject KEYUSE elements for their + IN-equalities. + +TODO: KEYUSE elements imply presense of KEYs! Which we don't have! + +3.4 JOIN_TAB sorting criteria +----------------------------- +Q: Where do we put JTBM's join_tab when pre-sorting records? +A: it should sort as regular table. + +TODO: where do we remove the predicates from the WHERE? + - remove them like SJ-converter does + - remove them with optimizer (like remove_eq_conds does) + +4. Optimization +=============== +Add a branch in best_access_path to account for +- JTBM-Materialization +- JTBM-Materialization-Scan. + +5. Execution +============ +* We should be able to reuse item_subselect.cc code for lookups +* But will have to use our own temptable scan code + +TODO: is it possible to have any unification with SJ-Materialization? + +User interface +-------------- +Any @@optimizer_switch flags for all this? + -=-=(Igor - Wed, 10 Mar 2010, 22:02)=-=- High Level Description modified. --- /tmp/wklog.90.old.2007 2010-03-10 22:02:23.000000000 +0000 +++ /tmp/wklog.90.new.2007 2010-03-10 22:02:23.000000000 +0000 @@ -13,8 +13,8 @@ for each record R2 in big_table such that oe=R1 pass R2 to output -Semi-join materialization supports such strategy with SJM-Scan strategy. This WL -entry is about adding support for such strategies for non-semijoin subqueries. +Semi-join materialization supports the inside-out strategy. This WL entry is +about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between -=-=(Igor - Wed, 10 Mar 2010, 21:52)=-=- Status updated. --- /tmp/wklog.90.old.882 2010-03-10 21:52:02.000000000 +0000 +++ /tmp/wklog.90.new.882 2010-03-10 21:52:02.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Sun, 28 Feb 2010, 15:37)=-=- High Level Description modified. --- /tmp/wklog.90.old.23524 2010-02-28 15:37:47.000000000 +0000 +++ /tmp/wklog.90.new.23524 2010-02-28 15:37:47.000000000 +0000 @@ -15,3 +15,7 @@ Semi-join materialization supports such strategy with SJM-Scan strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. + + +Once WL#89 is done, there will be a cost-based choice between +Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. -=-=(Psergey - Sun, 28 Feb 2010, 15:22)=-=- High-Level Specification modified. --- /tmp/wklog.90.old.23033 2010-02-28 15:22:09.000000000 +0000 +++ /tmp/wklog.90.new.23033 2010-02-28 15:22:09.000000000 +0000 @@ -1 +1,33 @@ +Basic idea on how this could be achieved: + +Pre-optimization phase +---------------------- + +The rewrite +~~~~~~~~~~~ +If we find a subquery predicate that is +- not processed by current semi-join optimizations +- is an AND-part of the WHERE/ON clause +- can be executed with Materialization + +then +- Remove the predicate from WHERE/ON clause +- Add a special JOIN_TAB object instead. + +Plan options +~~~~~~~~~~~~ +- Use the IN-equality to create KEYUSE elements. + +Optimization +------------ +- Pre-optimize the subquery so we know materialization cost +- Whenever best_access_path() encounters the "special JOIN_TAB" it should + consider two strategies: + A. Materialization and making lookups in the materialized table (if applicable) + B. Materialization and then scanning the materialized table. + + +EXPLAIN +------- +TODO how this will look in EXPLAIN output? -=-=(Psergey - Sun, 28 Feb 2010, 14:56)=-=- Dependency created: 91 now depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:54)=-=- Dependency deleted: 94 no longer depends on 90 -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21903 2010-02-28 14:47:54.000000000 +0000 +++ /tmp/wklog.90.new.21903 2010-02-28 14:47:54.000000000 +0000 @@ -1 +1 @@ - Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE +Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- High Level Description modified. --- /tmp/wklog.90.old.21880 2010-02-28 14:47:28.000000000 +0000 +++ /tmp/wklog.90.new.21880 2010-02-28 14:47:28.000000000 +0000 @@ -1,10 +1,17 @@ -For uncorrelated IN subqueries that can't be converted to semi-joins it is -necessary to make a cost-based choice between IN->EXISTS and Materialization -strategies. +Consider the following case: -Both strategies handle two cases: -1. A simple case w/o NULLs handling -2. Handling NULLs. +SELECT * FROM big_table +WHERE oe IN (SELECT ie FROM table_with_few_groups + WHERE ... + GROUP BY group_col) AND ... -This WL is about making cost-based decision for #1. +Here the best way to execute the query is: + Materialize the subquery; + # now run the join: + for each record R1 in materialized table + for each record R2 in big_table such that oe=R1 + pass R2 to output + +Semi-join materialization supports such strategy with SJM-Scan strategy. This WL +entry is about adding support for such strategies for non-semijoin subqueries. -=-=(Psergey - Sun, 28 Feb 2010, 14:47)=-=- Title modified. --- /tmp/wklog.90.old.21859 2010-02-28 14:47:02.000000000 +0000 +++ /tmp/wklog.90.new.21859 2010-02-28 14:47:02.000000000 +0000 @@ -1 +1 @@ -Subqueries: cost-based choice between Materialization and IN->EXISTS transformation + Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE ------------------------------------------------------------ -=-=(View All Progress Notes, 11 total)=-=- http://askmonty.org/worklog/index.pl?tid=90&nolimit=1 DESCRIPTION: Consider the following case: SELECT * FROM big_table WHERE oe IN (SELECT ie FROM table_with_few_groups WHERE ... GROUP BY group_col) AND ... Here the best way to execute the query is: Materialize the subquery; # now run the join: for each record R1 in materialized table for each record R2 in big_table such that oe=R1 pass R2 to output Semi-join materialization supports the inside-out strategy. This WL entry is about adding support for such strategies for non-semijoin subqueries. Once WL#89 is done, there will be a cost-based choice between Materialization+lookup, Materialization+scan, and IN->EXISTS+lookup strategies. HIGH-LEVEL SPECIFICATION: Basic idea on how this could be achieved: Pre-optimization phase ---------------------- The rewrite ~~~~~~~~~~~ If we find a subquery predicate that is - not processed by current semi-join optimizations - is an AND-part of the WHERE/ON clause - can be executed with Materialization then - Remove the predicate from WHERE/ON clause - Add a special JOIN_TAB object instead. Plan options ~~~~~~~~~~~~ - Use the IN-equality to create KEYUSE elements. Optimization ------------ - Pre-optimize the subquery so we know materialization cost - Whenever best_access_path() encounters the "special JOIN_TAB" it should consider two strategies: A. Materialization and making lookups in the materialized table (if applicable) B. Materialization and then scanning the materialized table. EXPLAIN ------- TODO how this will look in EXPLAIN output? LOW-LEVEL DESIGN: <contents> 1. Applicability check 2. Representation 2.1 Option #1: Convert to TABLE_LIST 2.2 On subquery predicate removal 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away 2.3 What is expected of the result of conversion 3. Pre-optimization steps 3.1 Constant detection 3.3 update_ref_and_keys 3.4 JOIN_TAB sorting criteria 4. Optimization 5. Execution User interface. </contents> We'll call the new execution strategy "jtbm-materialization", for the lack of better name. 1. Applicability check ====================== The criteria for checking whether a subquery can be processed with jtbm-materialization can be checked at JOIN::prepare stage (like it happens with semi-join check) 2. Representation ================= 2.1 Option #1: Convert to TABLE_LIST ------------------------------------ Make it work like semi-join nests: each jtbm-predicate is converted into a TABLE_LIST object. This will make it - uniform with semi-joins (we've stepped on all rakes there) - allow to process JTBM-subqueries in ON expressions simplify_joins() will handle jtbm TABLE_LISTs as some kinds of opaque base tables. for EXPLAIN EXTENDED, it would be natural to print something semi-join like, i.e. for SELECT ... FROM ot WHERE oe IN (SELECT ie FROM materialized-non-sj-select) we'll print SELECT ... FROM ot SJ (SELECT ie FROM materialized-non-sj-select) ON oe=XX the XX part is not clear. we don't want to print 'ie' the second time here? 2.2 On subquery predicate removal --------------------------------- Q: if we remove the subquery predicate permanently, who will run fix_fields() for it? For semi-joins we don't have the problem as we inject into ON expression (right? or not? we have sj_on_expr, too... (Investigation: we the the same Item* pointer both in WHERE and as sj_on_expr. fix_fields() is called for the WHERE part and that's how sj_on_expr gets fixed. This works as long as Item_func_eq::fix_fields() does not try to substitute itself with another item). A: ? 2.3 Option #2: No TABLE_LIST, convert to JOIN_TAB right away ------------------------------------------------------------ JOIN_TABs only live for the duration of one PS re-execution, so we'll have to - make conversion fully undoable - perform it sufficiently late in the optimization process, at the point where JOIN_TABs are already allocated. Note that if we don't position JTBM predicates in join's TABLE_LIST tree, then it will be impossible to handle JTBM queries inside/outside of outer joins. 2.3 What is expected of the result of conversion ------------------------------------------------ Join [pre]optimization relies on each optimized entity to have a bit in table_map. TODO: where do we check if there will be enough bits for everyone? (at the point where we assign them?) The bit stored in join_tab->table->map, and the apparent problem is that JTBM join_tabs do not naturally have TABLE* object. We could use the the one that will be used for Materialization, but that will stop working when we will have to include IN->EXISTS in the choice. Current approach: don't create a table. create a table_map element in JOIN_TAB instead. Evgen has probably done something like that already. 3. Pre-optimization steps ========================= JOIN_TABs are allocated in make_join_statistics(). This where the changes will be needed: for JOIN_TABs that correspond to JTBM-tables: - don't set tab->table, set tab->jtbm_select (or whatever) - run subquery's optimizer to get its output cardinality 3.1 Constant detection ---------------------- What about subqueries that "are constant"? const_item IN (SELECT uncorrelated) -> is constant, but not something we would want to evaluate. something IN (SELECT from_constant_join) -> is constant Do we need to mark their JOIN_TABs as constant? 3.3 update_ref_and_keys ----------------------- * Walk through JTBM elements and inject KEYUSE elements for their IN-equalities. TODO: KEYUSE elements imply presense of KEYs! Which we don't have! 3.4 JOIN_TAB sorting criteria ----------------------------- Q: Where do we put JTBM's join_tab when pre-sorting records? A: it should sort as regular table. TODO: where do we remove the predicates from the WHERE? - remove them like SJ-converter does - remove them with optimizer (like remove_eq_conds does) 4. Optimization =============== Add a branch in best_access_path to account for - JTBM-Materialization - JTBM-Materialization-Scan. 5. Execution ============ * We should be able to reuse item_subselect.cc code for lookups * But will have to use our own temptable scan code TODO: is it possible to have any unification with SJ-Materialization? User interface -------------- Any @@optimizer_switch flags for all this? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): New replication APIs (107)
by worklog-noreply＠askmonty.org 24 Mar '10

24 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: New replication APIs CREATION DATE..: Mon, 15 Mar 2010, 13:55 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: Sergei CATEGORY.......: Server-Sprint TASK ID........: 107 (http://askmonty.org/worklog/?tid=107) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 36 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Wed, 24 Mar 2010, 10:39)=-=- Design discussions Worked 11 hours and estimate 0 hours remain (original estimate increased by 11 hours). -=-=(Knielsen - Mon, 15 Mar 2010, 14:28)=-=- Research into the problem, and discussions on phone/mailing list Worked 25 hours and estimate 0 hours remain (original estimate increased by 25 hours). -=-=(Guest - Mon, 15 Mar 2010, 14:18)=-=- High-Level Specification modified. --- /tmp/wklog.107.old.9086 2010-03-15 14:18:18.000000000 +0000 +++ /tmp/wklog.107.new.9086 2010-03-15 14:18:18.000000000 +0000 @@ -1 +1,43 @@ +Current ideas/status after discussions on the mailing list: + + - Implement a set of plugin APIs and use them to move all of the existing + MySQL replication into a (set of) plugins. + + - Design the APIs so that they can support full MySQL replication, but also + so that they do not hardcode assumptions about how this replication + implementation is done, and so that they will be suitable for other types of + replication (Tungsten, Galera, parallel replication, ...). + + - APIs need to include the concept of a global transaction ID. Need to + determine the extent to which the semantics of such ID will be defined + by the API, and to which extend it will be defined by the plugin + implementations. + + - APIs should properly support reliable crash-recovery with decent + performance (eg. not require multiple mandatory fsync()s per commit, and + not make group commit impossible). + + - Would be nice if the API provided facilities for implementing good + consistency checking support (mainly checking master tables against slave + tables is hard here I think, but also applying wrong binlog data and + individual event checksums). + + +Steps to make this more concrete: + + - Investigate the current MySQL replication, and list all of the places where + a plugin implementation will need to connect/hook into the MySQL server. + * handler::{write,update,delete}_row() + * Statement execution + * Transaction start/commit + * Table open + * Query safe/not/safe for statement based replication + * Statement-based logging details (user variables, random seed, etc.) + * ... + + - Use this list to make an initial sketch of the set of APIs we need. + + - Use the list to determine the feasibility of this project and the level of + detail in the API needed to support a full replication implementation as a + plugin. -=-=(Serg - Mon, 15 Mar 2010, 14:13)=-=- Observers changed: Sergei DESCRIPTION: This is a top-level task for the project of designing a new set of replication APIs for MariaDB. This task is for the initial discussion of what to do and where to focus. The project is started in this email thread: https://lists.launchpad.net/maria-developers/msg01998.html HIGH-LEVEL SPECIFICATION: Current ideas/status after discussions on the mailing list: - Implement a set of plugin APIs and use them to move all of the existing MySQL replication into a (set of) plugins. - Design the APIs so that they can support full MySQL replication, but also so that they do not hardcode assumptions about how this replication implementation is done, and so that they will be suitable for other types of replication (Tungsten, Galera, parallel replication, ...). - APIs need to include the concept of a global transaction ID. Need to determine the extent to which the semantics of such ID will be defined by the API, and to which extend it will be defined by the plugin implementations. - APIs should properly support reliable crash-recovery with decent performance (eg. not require multiple mandatory fsync()s per commit, and not make group commit impossible). - Would be nice if the API provided facilities for implementing good consistency checking support (mainly checking master tables against slave tables is hard here I think, but also applying wrong binlog data and individual event checksums). Steps to make this more concrete: - Investigate the current MySQL replication, and list all of the places where a plugin implementation will need to connect/hook into the MySQL server. * handler::{write,update,delete}_row() * Statement execution * Transaction start/commit * Table open * Query safe/not/safe for statement based replication * Statement-based logging details (user variables, random seed, etc.) * ... - Use this list to make an initial sketch of the set of APIs we need. - Use the list to determine the feasibility of this project and the level of detail in the API needed to support a full replication implementation as a plugin. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Knielsen): New replication APIs (107)
by worklog-noreply＠askmonty.org 24 Mar '10

24 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: New replication APIs CREATION DATE..: Mon, 15 Mar 2010, 13:55 SUPERVISOR.....: Knielsen IMPLEMENTOR....: Knielsen COPIES TO......: Sergei CATEGORY.......: Server-Sprint TASK ID........: 107 (http://askmonty.org/worklog/?tid=107) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 36 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Wed, 24 Mar 2010, 10:39)=-=- Design discussions Worked 11 hours and estimate 0 hours remain (original estimate increased by 11 hours). -=-=(Knielsen - Mon, 15 Mar 2010, 14:28)=-=- Research into the problem, and discussions on phone/mailing list Worked 25 hours and estimate 0 hours remain (original estimate increased by 25 hours). -=-=(Guest - Mon, 15 Mar 2010, 14:18)=-=- High-Level Specification modified. --- /tmp/wklog.107.old.9086 2010-03-15 14:18:18.000000000 +0000 +++ /tmp/wklog.107.new.9086 2010-03-15 14:18:18.000000000 +0000 @@ -1 +1,43 @@ +Current ideas/status after discussions on the mailing list: + + - Implement a set of plugin APIs and use them to move all of the existing + MySQL replication into a (set of) plugins. + + - Design the APIs so that they can support full MySQL replication, but also + so that they do not hardcode assumptions about how this replication + implementation is done, and so that they will be suitable for other types of + replication (Tungsten, Galera, parallel replication, ...). + + - APIs need to include the concept of a global transaction ID. Need to + determine the extent to which the semantics of such ID will be defined + by the API, and to which extend it will be defined by the plugin + implementations. + + - APIs should properly support reliable crash-recovery with decent + performance (eg. not require multiple mandatory fsync()s per commit, and + not make group commit impossible). + + - Would be nice if the API provided facilities for implementing good + consistency checking support (mainly checking master tables against slave + tables is hard here I think, but also applying wrong binlog data and + individual event checksums). + + +Steps to make this more concrete: + + - Investigate the current MySQL replication, and list all of the places where + a plugin implementation will need to connect/hook into the MySQL server. + * handler::{write,update,delete}_row() + * Statement execution + * Transaction start/commit + * Table open + * Query safe/not/safe for statement based replication + * Statement-based logging details (user variables, random seed, etc.) + * ... + + - Use this list to make an initial sketch of the set of APIs we need. + + - Use the list to determine the feasibility of this project and the level of + detail in the API needed to support a full replication implementation as a + plugin. -=-=(Serg - Mon, 15 Mar 2010, 14:13)=-=- Observers changed: Sergei DESCRIPTION: This is a top-level task for the project of designing a new set of replication APIs for MariaDB. This task is for the initial discussion of what to do and where to focus. The project is started in this email thread: https://lists.launchpad.net/maria-developers/msg01998.html HIGH-LEVEL SPECIFICATION: Current ideas/status after discussions on the mailing list: - Implement a set of plugin APIs and use them to move all of the existing MySQL replication into a (set of) plugins. - Design the APIs so that they can support full MySQL replication, but also so that they do not hardcode assumptions about how this replication implementation is done, and so that they will be suitable for other types of replication (Tungsten, Galera, parallel replication, ...). - APIs need to include the concept of a global transaction ID. Need to determine the extent to which the semantics of such ID will be defined by the API, and to which extend it will be defined by the plugin implementations. - APIs should properly support reliable crash-recovery with decent performance (eg. not require multiple mandatory fsync()s per commit, and not make group commit impossible). - Would be nice if the API provided facilities for implementing good consistency checking support (mainly checking master tables against slave tables is hard here I think, but also applying wrong binlog data and individual event checksums). Steps to make this more concrete: - Investigate the current MySQL replication, and list all of the places where a plugin implementation will need to connect/hook into the MySQL server. * handler::{write,update,delete}_row() * Statement execution * Transaction start/commit * Table open * Query safe/not/safe for statement based replication * Statement-based logging details (user variables, random seed, etc.) * ... - Use this list to make an initial sketch of the set of APIs we need. - Use the list to determine the feasibility of this project and the level of detail in the API needed to support a full replication implementation as a plugin. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0