Use dynamic join filter to optimize hash join performance - AnalyticDB

Hash joins handle more than 95% of correlated queries in AnalyticDB for PostgreSQL. When the left table is large and only a small fraction of its rows match the right table, a standard hash join still scans every row in the left table—driving up disk I/O, network traffic, and CPU usage before the join even begins.

Dynamic join filter eliminates this waste. At runtime, it collects join keys from the right table and pushes that key set down into the left-table scan. Only matching rows are read and shipped across nodes, reducing overhead before the join executes.

Prerequisites

Dynamic join filter requires all of the following conditions.

Requirement	Details
Version	AnalyticDB for PostgreSQL V6.3.8.0 or later. To upgrade, see Update the minor engine version.
Query optimizer	Legacy query optimizer. Dynamic join filter is not supported for the ORCA optimizer. See Optimize query performance.
Computing engine	Laser computing engine. See Use the Laser computing engine.
Transport protocol	User Datagram Protocol (UDP). Dynamic join filter is not supported over Transmission Control Protocol (TCP).
Execution plan	Queries whose execution plan contains `IntiPlan` are not supported.

Enable or disable dynamic join filter

Dynamic join filter is enabled by default. Use the adbpg_enable_dynamic_join_filter parameter to control it at the session level.

Disable for the current session:

SET adbpg_enable_dynamic_join_filter TO off;

Enable for the current session:

SET adbpg_enable_dynamic_join_filter TO on;

To change the setting at the instance level, Submit a ticket.

Example: TPC-H Q17 benchmark

This example uses the lineitem and part tables from the TPC-H test set to measure the impact of dynamic join filter on TPC-H Q17.

Note

The TPC-H tests in this example are based on TPC-H benchmark methodology but do not satisfy all TPC-H benchmark requirements. The results are not comparable with published TPC-H benchmark results.

Set up the test data

Generate 1 GB of TPC-H test data. For instructions, see the "Generate test data" section in TPC-H performance testing.

Create the lineitem and part tables:

CREATE TABLE LINEITEM (
    L_ORDERKEY    BIGINT NOT NULL,
    L_PARTKEY     INTEGER NOT NULL,
    L_SUPPKEY     INTEGER NOT NULL,
    L_LINENUMBER  INTEGER NOT NULL,
    L_QUANTITY    NUMERIC(15,2) NOT NULL,
    L_EXTENDEDPRICE  NUMERIC(15,2) NOT NULL,
    L_DISCOUNT    NUMERIC(15,2) NOT NULL,
    L_TAX         NUMERIC(15,2) NOT NULL,
    L_RETURNFLAG  CHAR(1) NOT NULL,
    L_LINESTATUS  CHAR(1) NOT NULL,
    L_SHIPDATE    DATE NOT NULL,
    L_COMMITDATE  DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE     CHAR(10) NOT NULL,
    L_COMMENT      VARCHAR(44) NOT NULL
) WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN)
DISTRIBUTED BY (L_ORDERKEY, L_LINENUMBER);

CREATE TABLE PART (
    P_PARTKEY     INTEGER NOT NULL,
    P_NAME        VARCHAR(55) NOT NULL,
    P_MFGR        CHAR(25) NOT NULL,
    P_BRAND       CHAR(10) NOT NULL,
    P_TYPE        VARCHAR(25) NOT NULL,
    P_SIZE        INTEGER NOT NULL,
    P_CONTAINER   CHAR(10) NOT NULL,
    P_RETAILPRICE NUMERIC(15,2) NOT NULL,
    P_COMMENT     VARCHAR(23) NOT NULL
) WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN)
DISTRIBUTED BY (P_PARTKEY);

Load the test data:

\COPY LINEITEM FROM 'lineitem.tbl' with DELIMITER '|' NULL '';
\COPY PART FROM 'part.tbl' with DELIMITER '|' NULL '';

Run Q17 with dynamic join filter disabled

Disable dynamic join filter:

SET adbpg_enable_dynamic_join_filter TO off;

Run the Q17 query:

SELECT
    sum(l_extendedprice) / 7.0 as avg_yearly
FROM
    lineitem,
    part
WHERE
    p_partkey = l_partkey
    and p_brand = 'Brand#54'
    and p_container = 'SM CAN'
    and l_quantity < (
        SELECT
            0.2 * avg(l_quantity)
        FROM
            lineitem
        WHERE
            l_partkey = p_partkey
    );

The query completes in 3,468 ms:

avg_yearly
---------------------
 336452.465714285714
(1 row)

Time: 3468.411 ms

Run Q17 with dynamic join filter enabled

Enable dynamic join filter:

SET adbpg_enable_dynamic_join_filter TO on;

Run the same Q17 query.

The query completes in 305 ms:

avg_yearly
---------------------
 336452.465714285714
(1 row)

Time: 305.632 ms

Enabling dynamic join filter reduces query duration from 3,468 ms to 305 ms for the same result set.