There are several principles we can use in improving SQL performance.
One of them is :
"When there are complex OR predicates, rewrite the SQL as UNION ALL queries."
We separate the two parts of the OR predicates to help optimizer make the efficient plan with ease.
Here is the sample tables and SQL to demonstrate the principle above.
DROP TABLE 주문;
CREATE TABLE 주문 (
ORD_ID INT NOT NULL,
ORD_PROD_ID VARCHAR(2) NOT NULL,
ETC_CONTENT VARCHAR(100));
ALTER TABLE 주문 ADD CONSTRAINT 주문_PK PRIMARY KEY(ORD_ID);
CREATE INDEX 주문_X01 ON 주문(ORD_PROD_ID);
INSERT INTO 주문
SELECT i
,chr(64+case when i <= 10 then i else 26 end)
,rpad('x',100,'x')
FROM generate_series(1,1000000) a(i);
SELECT COUNT(*) FROM 주문 WHERE ORD_PROD_ID IN ('A','B','C');
DROP TABLE 배송;
CREATE TABLE 배송 (
ORD_ID INT NOT NULL,
VEHICLE_ID VARCHAR(2) NOT NULL,
ETC_REMARKS VARCHAR(100));
ALTER TABLE 배송 ADD CONSTRAINT 배송_PK primary key (ORD_ID, VEHICLE_ID);
CREATE INDEX 배송_X01 ON 배송(VEHICLE_ID);
INSERT INTO 배송
SELECT i
, chr(88 + case when i <= 10 then mod(i,2) else 2 end)
, rpad('x',100,'x')
FROM generate_series(1,1000000) a(i);
analyze 주문;
analyze 배송;
So here is the query we're interested in:
SELECT *
FROM 주문 a
WHERE ( EXISTS (SELECT 1
FROM 배송 b
WHERE a.ORD_ID = b.ORD_ID
AND b.VEHICLE_ID IN ('X','Y')
)
OR a.ORD_PROD_ID IN ('A','B','C')
);
Here's the execution plan.
Seq Scan on "주문" a (actual time=0.214..368.884 rows=10 loops=1) |
| Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR ((ord_prod_id)::text = ANY ('{A,B,C}'::text[]))) |
| Rows Removed by Filter: 999990 |
| Buffers: shared hit=5 read=17244 dirtied=17243 written=17210 |
| SubPlan 1 |
| -> Index Only Scan using "배송_pk" on "배송" b (never executed) |
| Index Cond: ((ord_id = a.ord_id) AND (vehicle_id = ANY ('{X,Y}'::text[]))) |
| Heap Fetches: 0 |
| SubPlan 2 |
| -> Bitmap Heap Scan on "배송" b_1 (actual time=0.054..0.056 rows=10 loops=1) |
| Recheck Cond: ((vehicle_id)::text = ANY ('{X,Y}'::text[])) |
| Heap Blocks: exact=1 |
| Buffers: shared hit=5 read=2 dirtied=1 |
| -> Bitmap Index Scan on "배송_x01" (actual time=0.036..0.036 rows=10 loops=1) |
| Index Cond: ((vehicle_id)::text = ANY ('{X,Y}'::text[])) |
| Buffers: shared hit=5 read=1 |
| Planning: |
| Buffers: shared hit=62 read=9 dirtied=1 |
| Planning Time: 1.530 ms |
| Execution Time: 368.956 ms
For every row in the 주문 table, PostgreSQL has checked whether or not the ORD_PROD_ID is an 'A','B' and 'C' and also checked SubPlan1 and SubPlan2 predicates.
It seems that the optimizer accesses 배송_x01 index and builds a hash table over ORD_IDs from the 배송 table in SubPlan2 and searches the ORD_IDs from the 주문 table in this hash table. That's what the "hashed Subplan2" means in the execution plan, I think.
I don't know why the Subplan2 was never executed.
I am afraid that I don't know what the final SQL transformed by the optimizer is.
I would appreciate it if anyone tells me how the optimzer transformed the SQL.
Even though 주문 table has indexes on ORD_PROD_ID column, the execution plan didn't use the index. This does not look efficient.
So let's apply the optimization principle
"When there are complex OR predicates, rewrite the SQL as UNION ALL queries."
to the SQL and rewrite it as follows:
SELECT *
FROM 주문 a
WHERE a.ORD_PROD_ID IN ('A','B','C')
UNION ALL
SELECT *
FROM 주문 a
WHERE EXISTS (SELECT 1
FROM 배송 b
WHERE a.ORD_ID = b.ORD_ID
AND b.VEHICLE_ID IN ('X','Y')
)
AND COALESCE(NOT(A.ORD_PROD_ID IN('A','B','C')),TRUE); --to prevent reporting twice
I used COALESCE(NOT(your condition),TRUE) function so as not to report any items twice.
Here's the resulting execution plan
Append (actual time=0.065..0.133 rows=10 loops=1) |
| Buffers: shared hit=50 read=7 |
| -> Bitmap Heap Scan on "주문" a (actual time=0.065..0.066 rows=3 loops=1) |
| Recheck Cond: ((ord_prod_id)::text = ANY ('{A,B,C}'::text[])) |
| Heap Blocks: exact=1 |
| Buffers: shared hit=6 read=4 |
| -> Bitmap Index Scan on "주문_x01" (actual time=0.048..0.048 rows=3 loops=1) |
| Index Cond: ((ord_prod_id)::text = ANY ('{A,B,C}'::text[])) |
| Buffers: shared hit=6 read=3 |
| -> Nested Loop (actual time=0.044..0.065 rows=7 loops=1) |
| Buffers: shared hit=44 read=3 |
| -> HashAggregate (actual time=0.016..0.019 rows=10 loops=1) |
| Group Key: b.ord_id |
| Batches: 1 Memory Usage: 40kB |
| Buffers: shared hit=7 |
| -> Bitmap Heap Scan on "배송" b (actual time=0.008..0.009 rows=10 loops=1) |
| Recheck Cond: ((vehicle_id)::text = ANY ('{X,Y}'::text[])) |
| Heap Blocks: exact=1 |
| Buffers: shared hit=7 |
| -> Bitmap Index Scan on "배송_x01" (actual time=0.007..0.007 rows=10 loops=1) |
| Index Cond: ((vehicle_id)::text = ANY ('{X,Y}'::text[])) |
| Buffers: shared hit=6 |
| -> Index Scan using "주문_pk" on "주문" a_1 (actual time=0.004..0.004 rows=1 loops=10) |
| Index Cond: (ord_id = b.ord_id) |
| Filter: COALESCE(((ord_prod_id)::text <> ALL ('{A,B,C}'::text[])), true) |
| Rows Removed by Filter: 0 |
| Buffers: shared hit=37 read=3 |
| Planning Time: 1.921 ms |
| Execution Time: 0.191 ms
Now we can see an efficient access into 주문 table to identify the 'A','B','C' rows.
In the second branch of the UNION ALL the EXISTS subquery collapsed and PostgreSQL was able to select the 10 rows from 배송 where vehicle_id is 'X' or 'Y' and used those 10 rows in a nested loop to drive into the 주문 table using the 주문_pk index. We can see the use of the function COALESCE that ensures we don't report the 'A','B','C' rows again.
Overall, the number of block I/Os decreased from 17249 to 57.
The elapsed time decreased from 368 to 0.191 msec.
Keep in mind the principle below.
"When there are complex OR predicates, rewrite the SQL as UNION ALL queries."
Surely, when the OR predicates are too complicated, you cannot apply the principle above and have to find out another trick to help the optimizer.
