Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23803

Fix pushdown select not to use TABLE and Field

    XMLWordPrintable

    Details

      Description

      The code behind pushdown select performs the following operations during Pushdown_select::init():

        handler->table= create_tmp_table(thd, &tmp_table_param, types,
                                         (ORDER *) 0, false, 0,
                                         TMP_TABLE_ALL_COLUMNS, 1,
                                         &empty_clex_str, true, false);
        if (!handler->table)
          DBUG_RETURN(true);
        if (handler->table->fill_item_list(&result_columns))
          DBUG_RETURN(true);
      

      That needs a lot of allocation and initialization:

      • TABLE - 976 bytes
      • TABLE_SHARE - 1424 bytes
      • An array of Fields inside TABLE - at least 208 bytes each Field
      • An array of Item_field, one Item for every Field - at least 320 bytes each

      In fact we don't need so many structures to perform a pushed select.

      Instead, we could allocate an array of literal Items, e.g. Item_int, Item_real, Item_string, Item_time_literal, etc, depending on the data type.

      A proof of concept patch

      The attached patch demonstrates this approach for a SELECT consisting of a single column, for the FederatedX storage engine, assuming a column of an integer data type.
      Note, the patch was tested only for the case when the results are sent to the client (e.g. not to another table in INSERT..SELECT or anywhere else, this needs some more coding).

      Testing the prototype

      Remote database DDL:

      CREATE OR REPLACE TABLE t1fedm (a INT);
      INSERT INTO t1fedm VALUES (1),(2),(3);
      

      Local database DDL:

      CREATE OR REPLACE TABLE t1fedm (
        a INT DEFAULT NULL
      ) ENGINE=FEDERATED CONNECTION='mysql://root:@192.168.0.10:3307/test/t1fedm';
      

      Local database query:

      SET global federated_pushdown=0;
      SELECT * FROM t1fedm;
      SET global federated_pushdown=1;
      SELECT * FROM t1fedm;
      

      MariaDB [test]> set global federated_pushdown=0;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> SELECT a FROM t1fedm;
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      | NULL |
      | NULL |
      +------+
      5 rows in set (0.006 sec)
       
      MariaDB [test]> set global federated_pushdown=1;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> SELECT a FROM t1fedm;
      +------+
      | hack |  <-- Notice the Item_int created in the experimental Pushdown_select::init() code
      +------+
      |    1 |
      |    2 |
      |    3 |
      | NULL |
      | NULL |
      +------+
      5 rows in set (0.003 sec)
      

      Notice, the new concept prototype code returns a correct result set, the same to the non-pushed query.

      Further improvement directions

      As the next step, we could also get rid of literal Items and replace them to:

      • an array of generic value containers (either in ValueBuffer or in NativeBuffer), or just an iterator, so we need just one value container
      • new virtual methods in Type_handler to populate a value container
      • new virtual methods in Type_handler to send a value containers to the result sink

      Benefits:

      • This array can be created during the first pushed SELECT and later reused by consequent pushed SELECTs.
      • Protocol::send_result_set_row() already uses ValueBuffer, so this code can be shared with pushed SELECTs.
      • TODO: think what other benefits (over the array of literal Items) we can gain with this approach

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: