Hologres:Runtime Filter

Scenarios

Hologres V2.0 and later supports Runtime Filter. It is typically used in hash join scenarios that involve two or more tables, especially when joining a large table with a small table. No manual configuration is required. The optimizer and execution engine automatically optimize filtering during query execution. This reduces I/O overhead and improves join query performance.

How it works

To understand how Runtime Filter works, you must first understand the join process. The following is a sample SQL statement for joining two tables:

select * from test1 join test2 on test1.x = test2.x;

The corresponding execution plan is shown below.

As shown in the execution plan, when the two tables are joined, a hash table is built from the test2 table. Then, the data from the test1 table is matched against the hash table. Finally, the result is returned. This join process involves two key terms:

• Build side: When two tables or subqueries are joined using a hash join, the data from one table or subquery is used to build a hash table. This side is called the build side. It corresponds to the Hash node in the execution plan.

• Probe side: The other side of the hash join reads data and matches it against the hash table from the build side. This side is called the probe side.

Typically, if the execution plan is correct, the smaller table is on the build side and the larger table is on the probe side.

Runtime Filter uses the data distribution of the build side to generate a lightweight filter. This filter is sent to the probe side to prune its data. This process reduces the volume of data from the probe side that is involved in the hash join and transmitted over the network, which improves hash join performance.

Therefore, Runtime Filter is most effective for joins between a large table and a small table, where the difference in data volume is significant. In these scenarios, it provides a greater performance improvement than a standard join.

Limits

• Only Hologres V2.0 and later supports Runtime Filter.

• Runtime Filter is triggered only if the join condition contains a single field. However, starting with Hologres V2.1, Runtime Filter supports joins on multiple fields and is triggered if multiple join fields meet the conditions.

• Only Hologres V4.0 and later supports TopN Runtime Filter. This feature is used to improve performance in scenarios that involve calculating TopN on a single table.

Trigger conditions

Hologres provides high-performance joins. The engine automatically triggers Runtime Filter if the SQL statement meets all of the following conditions:

• The data volume on the probe side is 100,000 rows or more.

• Ratio of scanned data volume: build side / probe side <= 0.1. The smaller the ratio, the more likely Runtime Filter is to be triggered.

• Ratio of joined data volume: build side / probe side <= 0.1. The smaller the ratio, the more likely Runtime Filter is to be triggered.

Example 1: Use a Local Runtime Filter for a join condition with one column

• Sample code:

  begin; 
  create table test1(x int, y int);
  call set_table_property('test1', 'distribution_key', 'x');
  
  create table test2(x int, y int);
  call set_table_property('test2', 'distribution_key', 'x');
  end;
  
  insert into test1 select t, t from generate_series(1, 100000) t;
  insert into test2 select t, t from generate_series(1, 1000) t;
  analyze test1;
  analyze test2;
  
  explain analyze select * from test1 join test2 on test1.x = test2.x;

• Execution plan:

  • The test2 table has 1,000 rows, and the test1 table has 100,000 rows. The ratio of data volume between the build side and the probe side is 0.01, and the ratio of joined data volume is also 0.01. Both are less than 0.1. Because these conditions meet the default trigger conditions, the engine automatically uses Runtime Filter.

  • The test1 table on the probe side has a Runtime Filter Target Expr node. This indicates that a Runtime Filter was pushed down to the probe side.

  • On the probe side, `scan_rows` represents the data read from storage, which is 100,000 rows. `rows` represents the number of rows from the scan operator after filtering with Runtime Filter, which is 1,000 rows. The difference between these two values shows the filtering effect of Runtime Filter.

Example 2: Use a Local Runtime Filter for a join condition with multiple columns (supported in Hologres V2.1)

• Sample code:

  drop table if exists test1, test2;
  begin;
  create table test1(x int, y int);
  create table test2(x int, y int);
  end;
  insert into test1 select t, t from generate_series(1, 1000000) t;
  insert into test2 select t, t from generate_series(1, 1000) t;
  analyze test1;
  analyze test2;
  
  explain analyze select * from test1 join test2 on test1.x = test2.x and test1.y = test2.y;

• Execution plan:

  • The join condition has multiple columns, and the Runtime Filter is also generated for multiple columns.

  • The build side is broadcast, so a Local Runtime Filter can be used.

Example 3: Use a Global Runtime Filter for a shuffle join (supported in Hologres V2.2)

• Sample code:

  SET hg_experimental_enable_result_cache = OFF;
  
  drop table if exists test1, test2;
  begin;
  create table test1(x int, y int);
  create table test2(x int, y int);
  end;
  insert into test1 select t, t from generate_series(1, 100000) t;
  insert into test2 select t, t from generate_series(1, 1000) t;
  analyze test1;
  analyze test2;
  
  explain analyze select * from test1 join test2 on test1.x = test2.x;

• Execution plan:

  The execution plan shows that the probe side data is shuffled to the Hash Join operator. The engine automatically uses a Global Runtime Filter to accelerate the query.

Example 4: Combine an In filter with a bitmap index (supported in Hologres V2.2)

• Sample code:

  set hg_experimental_enable_result_cache=off;
  
  drop table if exists test1, test2;
  
  begin;
  create table test1(x text, y text);
  call set_table_property('test1', 'distribution_key', 'x');
  call set_table_property('test1', 'bitmap_columns', 'x');
  call set_table_property('test1', 'dictionary_encoding_columns', '');
  
  create table test2(x text, y text);
  call set_table_property('test2', 'distribution_key', 'x');
  end;
  
  insert into test1 select t::text, t::text from generate_series(1, 10000000) t;
  
  insert into test2 select t::text, t::text from generate_series(1, 50) t;
  
  analyze test1;
  analyze test2;
  
  explain analyze select * from test1 join test2 on test1.x = test2.x;

• Execution plan:

  The execution plan shows that a bitmap is used on the scan operator of the probe side. The In filter provides precise filtering, reducing the output to only 50 rows. The `scan_rows` value is over 7 million, which is less than the original 10 million rows. This reduction occurs because the In filter can be pushed down to the storage engine, which can reduce I/O costs. Combining an In-type Runtime Filter with a bitmap is especially effective when the join key is a STRING type.

Example 5: Reduce I/O with a MinMax filter (supported in Hologres V2.2)

• Sample code:

  set hg_experimental_enable_result_cache=off;
  
  drop table if exists test1, test2;
  
  begin;
  create table test1(x int, y int);
  call set_table_property('test1', 'distribution_key', 'x');
  
  create table test2(x int, y int);
  call set_table_property('test2', 'distribution_key', 'x');
  end;
  
  insert into test1 select t::int, t::int from generate_series(1, 10000000) t;
  insert into test2 select t::int, t::int from generate_series(1, 100000) t;
  
  analyze test1;
  analyze test2;
  
  explain analyze select * from test1 join test2 on test1.x = test2.x;

• Execution plan:

  The execution plan shows that the scan operator on the probe side reads only a little over 320,000 rows from the storage engine. This is much less than the original 10 million rows. This reduction occurs because the Runtime Filter is pushed down to the storage engine. It uses the metadata of a data block to filter the entire block, which can significantly reduce I/O costs. This method is particularly effective when the join key is a numeric type and the value range of the build side is smaller than that of the probe side.