Boost Query Performance by Replacing OR with UNION ALL - PolarDB

When an OR condition spans multiple tables in a join query, the PostgreSQL optimizer treats the entire clause as a post-join filter and cannot use indexes on either table. This causes full table scans even when suitable indexes exist. PolarDB for PostgreSQL automatically rewrites qualifying cross-table OR clauses into UNION ALL branches during execution plan generation, enabling index use on each branch and reducing query execution time.

How it works

The PostgreSQL optimizer handles OR conditions differently depending on how many tables the clause spans.

Single-table OR clauses. When all conditions reference one table and each column has an index, the optimizer generates a BitmapOr path that combines multiple index scans. This works reasonably well for single-table queries, but BitmapOr is a fallback — not an optimal strategy.

Cross-table OR clauses. When an OR condition spans multiple tables, BitmapOr does not apply. The optimizer treats the entire clause as a single Join Filter applied after the join:

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=110 width=51) (actual time=5.992..15.673 rows=110 loops=1)
   Hash Cond: (t1.id = t2.id)
   Join Filter: ((t1.num = 1) OR (t2.cnt = 2))
   Rows Removed by Join Filter: 9890
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.012..2.080 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=5.855..5.857 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.007..1.779 rows=10000 loops=1)
 Planning Time: 1.237 ms
 Execution Time: 15.836 ms
(10 rows)

The clause (t1.num = 1 OR t2.cnt = 2) prevents index use on t1.num and t2.cnt, causing full table scans on both tables.

What the conversion does. The clause is logically equivalent to two independent queries combined with UNION ALL:

-- Branch 1: rows where t1.num = 1
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.num = 1
UNION ALL
-- Branch 2: rows where t2.cnt = 2, excluding rows already matched by branch 1
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.cnt = 2 AND (t1.num != 1 OR (t1.num = 1) IS NULL);

Each branch targets a single condition, letting the optimizer use the index on t1.num and t2.cnt respectively. PolarDB for PostgreSQL performs this rewrite automatically during execution plan generation and selects the converted path only when cost estimates favor it.

Prerequisites

This feature requires PolarDB for PostgreSQL running PostgreSQL 14 with minor engine version 2.0.14.13.27.0 or later.

Before you enable this feature, check your current version:

SHOW polardb_version;

Alternatively, view the minor engine version in the PolarDB console. If the version does not meet the requirement, upgrade the minor engine version before proceeding.

Enable the conversion

Two parameters control the OR-to-UNION-ALL conversion.

Parameter	Default	Description
`polar_cbqt_convert_or_to_union_all_mode`	`OFF`	Controls whether the optimizer attempts the conversion. `OFF`: disabled. `ON`: enabled, cost-based. `FORCE`: always choose the converted path if one exists, regardless of cost.
`polar_cbqt_cost_threshold`	`50000`	The minimum total cost an execution plan must have before the optimizer attempts conversion. Plans with a total cost at or below this threshold are skipped. Value range: `[0, +∞)`.

To enable cost-based conversion for a session:

SET polar_cbqt_convert_or_to_union_all_mode TO on;
SET polar_cbqt_cost_threshold TO 100;  -- Adjust based on your workload

Warning

Setting polar_cbqt_cost_threshold to 0 makes every SQL statement a candidate for conversion, which increases planning time across the board. Do not set this in production.

Statement-level control with HINTs. To force the conversion for a single SQL statement without changing session parameters, embed a HINT directly in the query:

/*+ Set(polar_cbqt_convert_or_to_union_all_mode force) Set(polar_cbqt_cost_threshold 0) */

This approach is preferable to setting FORCE at the session level, which affects all statements.

Limitations

Limitation	Detail
Single-table OR clauses	If all conditions in an OR clause reference the same table, the optimizer uses the existing `BitmapOr` path instead. Conversion is not attempted.
OR clause argument limit	If a single OR clause has more than six conditions, the optimizer skips conversion for that clause.
OR clause count limit per statement	If a SQL statement contains more than six OR clauses, the optimizer evaluates only the first six for conversion.
FORCE mode cost selection	In FORCE mode, when a statement has multiple qualifying OR clauses, the optimizer picks the converted plan with the lowest total cost among the candidates.
Cost threshold applies in FORCE mode	Even in FORCE mode, if the original plan's total cost is at or below `polar_cbqt_cost_threshold`, the optimizer does not attempt conversion.

Examples

Prepare the sample data

CREATE TABLE t1(id int, num int, dsc text, log_date text);
CREATE TABLE t2(id int, cnt int, change text, op_date text);

INSERT INTO t1 SELECT i, i%100, 'test'||1, to_char('1990-10-10'::date + i, 'YYYY-MM-DD') FROM generate_series(1,10000)i;
INSERT INTO t2 SELECT i, i%1000, 'now'||i, to_char('1990-10-10'::date + i, 'YYYY-MM-DD') FROM generate_series(1,10000)i;

CREATE INDEX ON t1(id);
CREATE INDEX ON t1(num);
CREATE INDEX ON t2(id);
CREATE INDEX ON t2(cnt);

ANALYZE t1;
ANALYZE t2;

Basic feature

Disabled (polar_cbqt_convert_or_to_union_all_mode = OFF):

SET polar_cbqt_cost_threshold TO 100;
SET polar_cbqt_convert_or_to_union_all_mode TO off;

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=110 width=51) (actual time=5.992..15.673 rows=110 loops=1)
   Hash Cond: (t1.id = t2.id)
   Join Filter: ((t1.num = 1) OR (t2.cnt = 2))
   Rows Removed by Join Filter: 9890
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.012..2.080 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=5.855..5.857 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.007..1.779 rows=10000 loops=1)
 Planning Time: 1.237 ms
 Execution Time: 15.836 ms
(10 rows)

Enabled (polar_cbqt_convert_or_to_union_all_mode = ON):

SET polar_cbqt_cost_threshold TO 100;
SET polar_cbqt_convert_or_to_union_all_mode TO on;

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);

                                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=85.48..411.16 rows=110 width=51) (actual time=0.396..4.822 rows=110 loops=1)
   ->  Hash Join  (cost=85.48..297.98 rows=100 width=51) (actual time=0.395..4.639 rows=100 loops=1)
         Hash Cond: (t2.id = t1.id)
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.010..1.750 rows=10000 loops=1)
         ->  Hash  (cost=84.23..84.23 rows=100 width=25) (actual time=0.333..0.335 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 14kB
               ->  Bitmap Heap Scan on t1  (cost=5.06..84.23 rows=100 width=25) (actual time=0.056..0.247 rows=100 loops=1)
                     Recheck Cond: (num = 1)
                     Heap Blocks: exact=73
                     ->  Bitmap Index Scan on t1_num_idx  (cost=0.00..5.04 rows=100 width=0) (actual time=0.028..0.028 rows=100 loops=1)
                           Index Cond: (num = 1)
   ->  Nested Loop  (cost=4.65..112.63 rows=10 width=51) (actual time=0.049..0.164 rows=10 loops=1)
         ->  Bitmap Heap Scan on t2  (cost=4.36..33.46 rows=10 width=26) (actual time=0.027..0.044 rows=10 loops=1)
               Recheck Cond: (cnt = 2)
               Heap Blocks: exact=10
               ->  Bitmap Index Scan on t2_cnt_idx  (cost=0.00..4.36 rows=10 width=0) (actual time=0.019..0.019 rows=10 loops=1)
                     Index Cond: (cnt = 2)
         ->  Index Scan using t1_id_idx on t1  (cost=0.29..7.91 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=10)
               Index Cond: (id = t2.id)
               Filter: ((num <> 1) OR ((num = 1) IS NULL))
 Planning Time: 2.903 ms
 Execution Time: 4.980 ms
(22 rows)

With conversion enabled, the optimizer generates an Append plan that uses Bitmap Index Scan on t1.num and t2.cnt. Execution time drops from 15.836 ms to 4.980 ms.

Force selection

In ON mode, the optimizer converts an OR clause only when the converted path has a lower estimated cost. For queries where the optimizer's cost estimates are inaccurate, use FORCE mode to override the cost-based decision.

ON mode — optimizer does not choose the converted path because it estimates a higher cost:

SET polar_cbqt_cost_threshold TO 100;
SET polar_cbqt_convert_or_to_union_all_mode TO on;

EXPLAIN ANALYZE SELECT * FROM t1, t2 WHERE t1.dsc = t2.change AND (t1.log_date = '2024-01-01' OR t2.op_date = '2024-01-01');

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=2 width=51) (actual time=14.321..14.325 rows=0 loops=1)
   Hash Cond: (t1.dsc = t2.change)
   Join Filter: ((t1.log_date = '2024-01-01'::text) OR (t2.op_date = '2024-01-01'::text))
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.016..3.204 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=6.506..6.508 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.006..1.755 rows=10000 loops=1)
 Planning Time: 0.932 ms
 Execution Time: 14.571 ms
(9 rows)

FORCE mode — optimizer chooses the converted path regardless of cost:

SET polar_cbqt_cost_threshold TO 100;
SET polar_cbqt_convert_or_to_union_all_mode TO force;

EXPLAIN ANALYZE SELECT * FROM t1, t2 WHERE t1.dsc = t2.change AND (t1.log_date = '2024-01-01' OR t2.op_date = '2024-01-01');

                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Append  (cost=199.01..871.05 rows=2 width=51) (actual time=9.915..9.923 rows=0 loops=1)
   ->  Hash Join  (cost=199.01..410.52 rows=1 width=51) (actual time=5.046..5.050 rows=0 loops=1)
         Hash Cond: (t2.change = t1.dsc)
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.015..0.015 rows=1 loops=1)
         ->  Hash  (cost=199.00..199.00 rows=1 width=25) (actual time=5.014..5.016 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t1  (cost=0.00..199.00 rows=1 width=25) (actual time=5.013..5.013 rows=0 loops=1)
                     Filter: (log_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
   ->  Hash Join  (cost=199.01..460.52 rows=1 width=51) (actual time=4.865..4.867 rows=0 loops=1)
         Hash Cond: (t1.dsc = t2.change)
         ->  Seq Scan on t1  (cost=0.00..224.00 rows=9999 width=25) (actual time=0.015..0.016 rows=1 loops=1)
               Filter: ((log_date <> '2024-01-01'::text) OR ((log_date = '2024-01-01'::text) IS NULL))
         ->  Hash  (cost=199.00..199.00 rows=1 width=26) (actual time=4.828..4.829 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t2  (cost=0.00..199.00 rows=1 width=26) (actual time=4.827..4.827 rows=0 loops=1)
                     Filter: (op_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
 Planning Time: 0.777 ms
 Execution Time: 10.088 ms
(20 rows)

In this case, the converted path has a higher estimated cost (871.05 vs 660.50) but lower actual execution time (10.088 ms vs 14.571 ms). Use FORCE mode when the optimizer's cost estimates are inaccurate and you know the converted plan performs better.

Use HINTs

To force the OR-to-UNION-ALL optimization for a single SQL statement without changing session parameters, embed a HINT in the query comment:

SET polar_cbqt_convert_or_to_union_all_mode TO off;

EXPLAIN ANALYZE /*+ Set(polar_cbqt_convert_or_to_union_all_mode force) Set(polar_cbqt_cost_threshold 0) */
SELECT * FROM t1, t2 WHERE t1.dsc = t2.change AND (t1.log_date = '2024-01-01' OR t2.op_date = '2024-01-01');

                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Append  (cost=199.01..871.05 rows=2 width=51) (actual time=9.684..9.691 rows=0 loops=1)
   ->  Hash Join  (cost=199.01..410.52 rows=1 width=51) (actual time=4.711..4.714 rows=0 loops=1)
         Hash Cond: (t2.change = t1.dsc)
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.013..0.013 rows=1 loops=1)
         ->  Hash  (cost=199.00..199.00 rows=1 width=25) (actual time=4.682..4.684 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t1  (cost=0.00..199.00 rows=1 width=25) (actual time=4.681..4.681 rows=0 loops=1)
                     Filter: (log_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
   ->  Hash Join  (cost=199.01..460.52 rows=1 width=51) (actual time=4.969..4.970 rows=0 loops=1)
         Hash Cond: (t1.dsc = t2.change)
         ->  Seq Scan on t1  (cost=0.00..224.00 rows=9999 width=25) (actual time=0.018..0.018 rows=1 loops=1)
               Filter: ((log_date <> '2024-01-01'::text) OR ((log_date = '2024-01-01'::text) IS NULL))
         ->  Hash  (cost=199.00..199.00 rows=1 width=26) (actual time=4.935..4.936 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on t2  (cost=0.00..199.00 rows=1 width=26) (actual time=4.934..4.934 rows=0 loops=1)
                     Filter: (op_date = '2024-01-01'::text)
                     Rows Removed by Filter: 10000
 Planning Time: 0.798 ms
 Execution Time: 9.858 ms
(20 rows)

The HINT overrides the session parameter and applies the FORCE conversion to this statement only.

Conversion threshold

The cost threshold prevents the optimizer from attempting conversion on cheap queries. Even in FORCE mode, if a query's original plan cost is at or below polar_cbqt_cost_threshold, no conversion is attempted.

In the basic feature example above, the original plan has a total cost of 660.50. Setting the threshold above 660.50 suppresses conversion:

SET polar_cbqt_cost_threshold TO 1000;
SET polar_cbqt_convert_or_to_union_all_mode TO force;

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE (t1.num = 1 OR t2.cnt = 2);

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=299.00..660.50 rows=110 width=51) (actual time=6.374..15.802 rows=110 loops=1)
   Hash Cond: (t1.id = t2.id)
   Join Filter: ((t1.num = 1) OR (t2.cnt = 2))
   Rows Removed by Join Filter: 9890
   ->  Seq Scan on t1  (cost=0.00..174.00 rows=10000 width=25) (actual time=0.011..2.038 rows=10000 loops=1)
   ->  Hash  (cost=174.00..174.00 rows=10000 width=26) (actual time=6.266..6.268 rows=10000 loops=1)
         Buckets: 16384  Batches: 1  Memory Usage: 704kB
         ->  Seq Scan on t2  (cost=0.00..174.00 rows=10000 width=26) (actual time=0.006..1.778 rows=10000 loops=1)
 Planning Time: 0.663 ms
 Execution Time: 16.036 ms
(10 rows)

The original plan is used because its total cost (660.50) does not exceed the threshold (1000).