Join Operations

How to Force a Join Order

SereneDB has a cost-based query optimizer, which uses statistics in the base tables (stored in a SereneDB database or Parquet files) to estimate the cardinality of operations.

Turn off the Join Order Optimizer

To turn off the join order optimizer, set the following PRAGMAs:

Query

SET disabled_optimizers = 'join_order,build_side_probe_side';

This disables both the join order optimizer and left/right swapping for joins. This way, SereneDB builds a left-deep join tree following the order of JOIN clauses.

Query

SELECT    r.region_name,    c.name,    o.amountFROM regions AS rJOIN customers AS c ON c.region_id = r.region_idJOIN orders AS o ON o.customer_id = c.customer_idORDER BY o.amount;

Result

 region_name | name  | amount-------------+-------+-------- South       | Bob   |     30 North       | Alice |     50 North       | Alice |     75

Once the query in question has been executed, turn back the optimizers with the following command:

Query

SET disabled_optimizers = '';

Create Temporary Tables

To force a particular join order, you can break up the query into multiple queries, with each creating a temporary table:

Query

CREATE OR REPLACE TEMPORARY TABLE t1 AS    SELECT        r.region_id,        r.region_name,        c.customer_id,        c.name    FROM regions AS r    JOIN customers AS c ON c.region_id = r.region_id;
CREATE OR REPLACE TEMPORARY TABLE t2 AS    SELECT        t1.region_name,        t1.name,        o.amount    FROM t1    JOIN orders AS o ON o.customer_id = t1.customer_id;
SELECT *FROM t2ORDER BY amount;

Result

 region_name | name  | amount-------------+-------+-------- South       | Bob   |     30 North       | Alice |     50 North       | Alice |     75

To clean up, drop the interim tables:

Query

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;