This is the second example to demonstrate the tuning technique :
"When there are complex OR predicates, rewrite the SQL as UNION ALL queries."
We separate the two parts of the OR predicates to help the optimizer build an efficient plan with ease.
Here are the sample tables and SQL statements to demonstrate the principle above. The following scripts were run under PostgreSQL 13.
CREATE TABLE ords (
ORD_ID INT NOT NULL,
CUST_ID VARCHAR(10) NOT NULL,
ORD_DATE DATE NOT NULL,
ETC_CONTENT VARCHAR(100));
ALTER TABLE ords ADD CONSTRAINT ORDS_PK PRIMARY KEY(ORD_ID);
CREATE INDEX ORDS_X01 ON ORDS (CUST_ID);
INSERT INTO ORDS
SELECT i
,lpad(mod(i,1000)::text,10,'cust')
,date '2021-06-07'+mod(i,624)
,rpad('x',100,'x')
FROM generate_series(1,1000000) a(i);
CREATE TABLE delivery (
ORD_ID INT NOT NULL,
VEHICLE_ID VARCHAR(10) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
ETC_REMARKS VARCHAR(100));
INSERT INTO DELIVERY
SELECT i
, MOD(i,1000)
, date '2021-01-01' + mod(i,1000)
, date '2021-01-05' + mod(i,1000)
, rpad('x',100,'x')
FROM generate_series(1,1000000) a(i);
ALTER TABLE DELIVERY ADD CONSTRAINT DELIVERY_PK primary key (ORD_ID);
CREATE INDEX DELIVERY_X01 ON DELIVERY(END_DATE, START_DATE);
CREATE INDEX DELIVERY_X02 ON DELIVERY(VEHICLE_ID);
select pg_relation_size('ords'), pg_relation_size('delivery');
pg_relation_size | pg_relation_size
------------------+------------------
157540352 | 148946944
The following is the SQL statement we are trying to improve the performance of.
SELECT A.*, B.*
FROM ORDS A LEFT JOIN DELIVERY B
ON (A.ORD_ID = B.ORD_ID
AND (B.START_DATE <= DATE '2021-07-12' AND B.END_DATE >= DATE '2021-07-10'
OR (B.VEHICLE_ID > '990')
)
)
WHERE A.ORD_DATE BETWEEN DATE '2021-06-01' AND DATE '2021-07-10';
Here is the execution plan of the statement.
Gather (actual time=86.645..101.685 rows=54501 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13615 read=23995, temp read=1196 written=1272
-> Parallel Hash Left Join (actual time=83.360..87.135 rows=18167 loops=3)
Hash Cond: (a.ord_id = b.ord_id)
Buffers: shared hit=13614 read=23995, temp read=1196 written=1272
-> Parallel Seq Scan on ords a (actual time=0.047..34.335 rows=18167 loops=3)
Filter: ((ord_date >= '2021-06-01'::date) AND (ord_date <= '2021-07-10'::date))
Rows Removed by Filter: 315166
Buffers: shared hit=4968 read=14263
-> Parallel Hash (actual time=42.999..42.999 rows=5333 loops=3)
Buckets: 32768 Batches: 8 Memory Usage: 608kB
Buffers: shared hit=8450 read=9732, temp written=280
-> Parallel Seq Scan on delivery b (actual time=0.069..40.615 rows=5333 loops=3)
Filter: (((start_date <= '2021-07-12'::date) AND
(end_date >= '2021-07-10'::date)) OR ((vehicle_id)::text >
'990'::text))
Rows Removed by Filter: 328000
Buffers: shared hit=8450 read=9732
Planning:
Buffers: shared hit=20
Planning Time: 0.357 ms
Execution Time: 103.282 ms
The
optimizer has let me down here. It didn't access any indexes in the
DELIVERY table. The plan shows us that we have to scan all the data from
the DELIVERY table before applying the filter condition and discarding
about (1000000 - (5333 x 3)) rows. (We can check the Rows Removed by Filter item to see that one of the worker processes discarded 328000 rows.) Depending
on the pattern of data in the table, this strategy can be efficient.
However, we shouldn't put full trust in the planner's decision. I
had expected that two Bitmap Index Scans accessing the delivery_x01 and
delivery_x02 would appear followed by the BitmapOr operation when
fetching rows from the DELIVERY table. Unlike what I anticipated, the planner chose to do a table scan with the parallelism.
To
compare the execution plan I expected with the plan PostgreSQL chose, I
set the parameter max_parallel_workers_per_gather to 0 and re-ran the
SQL statement.
set max_parallel_workers_per_gather = 0;
--I re-ran the query under investigation and here is the resulting execution plan.
Hash Right Join (actual time=100.080..119.375 rows=54501 loops=1)
Hash Cond: (b.ord_id = a.ord_id)
Buffers: shared hit=3304 read=18847, temp read=903 written=903
-> Bitmap Heap Scan on delivery b (actual time=1.374..4.277 rows=16000 loops=1)
Recheck Cond: (((end_date >= '2021-07-10'::date) AND (start_date <= '2021-07-12'::date)) OR ((ve
hicle_id)::text > '990'::text))
Heap Blocks: exact=2182
Buffers: shared hit=2919
-> BitmapOr (actual time=1.108..1.109 rows=0 loops=1)
Buffers: shared hit=737
-> Bitmap Index Scan on delivery_x01 (actual time=0.809..0.810 rows=7000 loops=1)
Index Cond: ((end_date >= '2021-07-10'::date) AND (start_date <= '2021-07-12'::date))
Buffers: shared hit=726
-> Bitmap Index Scan on delivery_x02 (actual time=0.298..0.298 rows=9000 loops=1)
Index Cond: ((vehicle_id)::text > '990'::text)
Buffers: shared hit=11
-> Hash (actual time=98.373..98.374 rows=54501 loops=1)
Buckets: 32768 Batches: 4 Memory Usage: 2331kB
Buffers: shared hit=384 read=18847, temp written=697
-> Seq Scan on ords a (actual time=0.122..85.072 rows=54501 loops=1)
Filter: ((ord_date >= '2021-06-01'::date) AND (ord_date <= '2021-07-10'::date))
Rows Removed by Filter: 945499
Buffers: shared hit=384 read=18847
Planning:
Buffers: shared hit=12
Planning Time: 0.232 ms
Execution Time: 120.843 ms
By
using Bitmap Index Scan and BitmapOr operations we could drop the
number of block I/Os, but the execution time increased from 103 ms to
120 ms. The elapsed time gap is due to the parallelization. We can infer
that if parallelization works in the BitmapOr operation, we would get
the query faster. But unfortunately in the PostgreSQL version I am on,
which is 13, Bitmap Index Scan cannot use the parallelism.
Please refer to the following post regarding the bitmap index scan and the parallelism.
https://www.postgresql-archive.org/Parallel-bitmap-index-scan-td6147662.html
As
far as I know, there is no workaround to force the planner to use the
BitmapOr operation without setting max_parallel_workers_per_gather to 0.
When there is OR in the WHERE clause, the planner tends to prefer a table scan to an index scan.
Now let's apply the tuning principle
"When there are complex OR predicates, rewrite the SQL as UNION ALL queries."
to the SQL statement and re-engineer the code as follows:
SELECT A.*, B.*
FROM ORDS A
LEFT JOIN (SELECT *
FROM DELIVERY
WHERE VEHICLE_ID > '990'
UNION ALL
SELECT *
FROM DELIVERY
WHERE START_DATE <= DATE '2021-07-12' AND END_DATE >= DATE '2021-07-10'
AND COALESCE(NOT(VEHICLE_ID > '990'),TRUE)) B
ON A.ORD_ID = B.ORD_ID
WHERE A.ORD_DATE BETWEEN DATE '2021-06-01' AND DATE '2021-07-10';
I used COALESCE(NOT(VEHICLE_ID > '990'),TRUE) to prevent getting rows twice.
Here is the resulting execution plan.
Hash Right Join (actual time=43.932..64.737 rows=54501 loops=1)
Hash Cond: (delivery.ord_id = a.ord_id)
Buffers: shared hit=7984 read=14167, temp read=903 written=903
-> Append (actual time=0.526..5.993 rows=16000 loops=1)
Buffers: shared hit=2919
-> Bitmap Heap Scan on delivery (actual time=0.526..2.550 rows=9000 loops=1)
Recheck Cond: ((vehicle_id)::text > '990'::text)
Heap Blocks: exact=1091
Buffers: shared hit=1102
-> Bitmap Index Scan on delivery_x02 (actual time=0.437..0.438 rows=9000 loops=1)
Index Cond: ((vehicle_id)::text > '990'::text)
Buffers: shared hit=11
-> Bitmap Heap Scan on delivery delivery_1 (actual time=0.965..2.596 rows=7000 loops=1
Recheck Cond: ((end_date >= '2021-07-10'::date) AND (start_date <= '2021-07-
Filter: COALESCE(((vehicle_id)::text <= '990'::text), true)
Heap Blocks: exact=1091
Buffers: shared hit=1817
-> Bitmap Index Scan on delivery_x01 (actual time=0.874..0.875 rows=7000 loops=1)
Index Cond: ((end_date >= '2021-07-10'::date) AND (start_date <= '2021-07-12'
Buffers: shared hit=726
-> Hash (actual time=43.131..43.182 rows=54501 loops=1)
Buckets: 32768 Batches: 4 Memory Usage: 2331kB
Buffers: shared hit=5064 read=14167, temp written=697
-> Gather (actual time=0.280..31.877 rows=54501 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5064 read=14167
-> Parallel Seq Scan on ords a (actual time=0.042..31.570 rows=18167 loops=3)
Filter: ((ord_date >= '2021-06-01'::date) AND (ord_date <= '2021-07-10'::date))
Rows Removed by Filter: 315166
Buffers: shared hit=5064 read=14167
Planning:
Buffers: shared hit=4
Planning Time: 0.215 ms
Execution Time: 66.304 ms
Notice
the appearance of Bitmap Index Scan in the above execution plan. By
re-engineering the query with UNION ALL, we could get more efficient
plan (Block I/Os and execution time dropped). Even though the
parallelism doesn't kick in, we get the query faster.
Will
this tuning technique always work? No, not necessarily always. If there
are not optimal indexes, rewriting the query as UNION ALL queries
degrades performance.
Addendum
We
might consider using a lateral join in the revised query. PostgreSQL
iterates over each of the row of ORDS and runs UNION ALL subquery giving
an access to that row. Since there are 54501 rows in the results of
ORDS, it doesn't seem to be a good strategy. For your reference, I used a
lateral join and presented the execution plan here:
SELECT A.*, B.*
FROM ORDS A
LEFT JOIN LATERAL (SELECT *
FROM DELIVERY B
WHERE VEHICLE_ID > '990'
AND A.ORD_ID = B.ORD_ID
UNION ALL
SELECT *
FROM DELIVERY B
WHERE START_DATE <= DATE '2021-07-12' AND END_DATE >= DATE '2021-07-10'
AND COALESCE(NOT(VEHICLE_ID > '990'),TRUE)
AND A.ORD_ID = B.ORD_ID) B ON TRUE
WHERE A.ORD_DATE BETWEEN DATE '2021-06-01' AND DATE '2021-07-10';
Nested Loop Left Join (actual time=0.118..218.668 rows=54501 loops=1)
Buffers: shared hit=436104 read=19135
-> Seq Scan on ords a (actual time=0.102..83.226 rows=54501 loops=1)
Filter: ((ord_date >= '2021-06-01'::date) AND (ord_date <= '2021-07-10'::date))
Rows Removed by Filter: 945499
Buffers: shared hit=96 read=19135
-> Append (actual time=0.002..0.002 rows=0 loops=54501)
Buffers: shared hit=436008
-> Index Scan using delivery_pk on delivery b_1 (actual time=0.001..0.001 rows=0 loops=54501)
Index Cond: (ord_id = a.ord_id)
Filter: ((vehicle_id)::text > '990'::text)
Rows Removed by Filter: 1
Buffers: shared hit=218004
-> Index Scan using delivery_pk on delivery b_2 (actual time=0.001..0.001 rows=0 loops=54501)
Index Cond: (ord_id = a.ord_id)
Filter: ((start_date <= '2021-07-12'::date) AND (end_date >= '2021-07-10'::date) AND COALESCE(((vehicle_id)::text
Rows Removed by Filter: 1
Buffers: shared hit=218004
Planning:
Buffers: shared hit=4
Planning Time: 0.252 ms
Execution Time: 221.052 ms
As we expected, this transformation lead to a decline in performance.
