Query Pushdown

This page outlines the different ways pushdown of a query to TileDB is supported

MyTile supports pushing down parts of a query from MariaDB to TileDB. Currently this is done through the cond_push support in MariaDB and we are evaluating the newer select_handler support introduced in MariaDB 10.4.

Joins

Joins are optimized with bulk pushdown of the join keys through batched key access.

Predicate Pushdown Support

MyTile analyzes the WHERE clause of SQL statements and pushes down the following conditions:

Type	Example
Basic Predicates	x = 10 x BETWEEN 0 and 11
Datetime Conditions	y <= '2021-01-01' y >= '2021-01-01' - INTERVAL 1 DAY
ASCII String Conditions	z = 'hello' z > 'he'

How It Works

Predicates on Dimensions

Predicates listed for a dimension are collected and turned into TileDB ranges for a multi-range query. Conditions here are merged into so called "super ranges" if there is overlap or adjacency. These are then issued as multi-range queries.

Predicates on Attributes

Predicates given for attributes are converted into TileDB Query Conditions that are applied to the attributes. Query conditions allow TileDB to filter cells based on the conditions at the the storage level in a highly efficient and multi-threaded manner.

Unsupported Pushdown

The following predicate types are not supported:

• Field comparisons x = y

• Function comparisons x = utc_timestamp()

• Geometric functions ST_WITHIN(x, polygon(point(1,2), point(2, 2), point(2, 1), point(1,1)))

Validating Pushdown

Running an EXPLAIN will show if the predicate pushdown is being used:

EXPLAIN SELECT * FROM `tiledb://TileDB-Inc/vepvariantannotation` WHERE pos_start = 69224;
+------+-------------+------------------------------------------+------+---------------+------+---------+------+--------+-----------------------------------+
| id   | select_type |                  table                   | type | possible_keys | key  | key_len | ref  | rows   | Extra                             |
+------+-------------+------------------------------------------+------+---------------+------+---------+------+--------+-----------------------------------+
|    1 | SIMPLE      | tiledb://TileDB-Inc/vepvariantannotation | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where with pushed condition |
+------+-------------+------------------------------------------+------+---------------+------+---------+------+--------+-----------------------------------+
1 row in set (0.001 sec)

Aggregate Pushdown

Currently aggregate pushdowns like COUNT(*) or AVG are not supported. Queries that include such functions will work as expected, however, MariaDB itself will perform these operations instead of TileDB. Aggregate, window functions and more are on the roadmap for futher pushdown where performance benefits can be gained.