測試環境：
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=#
數據準備：
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)
postgres=# select * from pgbench_accounts limit 1;
aid | bid | abalance | filler
-----+-----+----------+--------------------------------------------------------------------------------------
1 | 1 | 0 |
(1 row)
postgres=# select * from pgbench_branches limit 1;
bid | bbalance | filler
-----+----------+--------
1 | 0 |
(1 row)
postgres=# select * from pgbench_history limit 1;
tid | bid | aid | delta | mtime | filler
-----+-----+-----+-------+-------+--------
(0 rows)
postgres=# select * from pgbench_tellers limit 1;
tid | bid | tbalance | filler
-----+-----+----------+--------
1 | 1 | 0 |
(1 row)
postgres=# select * from pgbench_branches;
bid | bbalance | filler
-----+----------+--------
1 | 0 |
2 | 0 |
3 | 0 |
4 | 0 |
5 | 0 |
6 | 0 |
7 | 0 |
8 | 0 |
9 | 0 |
10 | 0 |
(10 rows)
postgres=# update pgbench_branches set bbalance=4500000 where bid in (4,7);
UPDATE 2
postgres=#
IN語句
查詢要求：找出那些余額(balance)大于0的每個分支(branch)在表在pgbench_accounts中有多少個賬戶
1.使用IN子句
SELECT
count( aid ),bid
FROM
pgbench_accounts
WHERE
bid IN ( SELECT bid FROM pgbench_branches WHERE bbalance > 0 )
GROUP BY
bid;
2.使用ANY子句
SELECT
count( aid ),bid
FROM
pgbench_accounts
WHERE
bid = ANY ( SELECT bid FROM pgbench_branches WHERE bbalance > 0 )
GROUP BY
bid;
3.使用EXISTS子句
SELECT
count( aid ),bid
FROM
pgbench_accounts
WHERE
EXISTS ( SELECT bid FROM pgbench_branches WHERE bbalance > 0 AND pgbench_accounts.bid = pgbench_branches.bid )
GROUP BY
bid;
4.使用INNER JOIN
SELECT
count( aid ),a.bid
FROM
pgbench_accounts a
JOIN pgbench_branches b ON a.bid = b.bid
WHERE
b.bbalance > 0
GROUP BY
a.bid;
在完成這個查詢要求的時候，有人可能會假設exists和inner join性能可能會更好，因為他們可以使用兩表連接的邏輯和優化。而IN和ANY子句需要使用子查詢。
然而，PostgreSQL(10版本之后)已經智能的足以對上面四種寫法產生相同的執行計劃！
所有上面的寫法都會產生相同的執行計劃：
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=23327.73..23330.26 rows=10 width=12) (actual time=97.199..99.014 rows=2 loops=1)
Group Key: a.bid
-> Gather Merge (cost=23327.73..23330.06 rows=20 width=12) (actual time=97.191..99.006 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=22327.70..22327.73 rows=10 width=12) (actual time=93.762..93.766 rows=2 loops=3)
Sort Key: a.bid
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=22327.44..22327.54 rows=10 width=12) (actual time=93.723..93.727 rows=2 loops=3)
Group Key: a.bid
-> Hash Join (cost=1.14..22119.10 rows=41667 width=8) (actual time=24.024..83.263 rows=66667 loops=3)
Hash Cond: (a.bid = b.bid)
-> Parallel Seq Scan on pgbench_accounts a (cost=0.00..20560.67 rows=416667 width=8) (actual time=0.023..43.151 rows=333333 loops=3)
-> Hash (cost=1.12..1.12 rows=1 width=4) (actual time=0.027..0.028 rows=2 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pgbench_branches b (cost=0.00..1.12 rows=1 width=4) (actual time=0.018..0.020 rows=2 loops=3)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.342 ms
Execution Time: 99.164 ms
(22 rows)
那么，我們是否可以得出這樣的結論:我們可以隨意地編寫查詢，而PostgreSQL的智能將會處理其余的問題?！
等等!
如果我們考慮排除情況，事情會變得不同。
排除查詢
查詢要求：找出那些余額(balance)不大于0的每個分支(branch)在表在pgbench_accounts中有多少個賬戶
1.使用NOT IN
SELECT
count( aid ),bid
FROM
pgbench_accounts
WHERE
bid NOT IN ( SELECT bid FROM pgbench_branches WHERE bbalance > 0 )
GROUP BY
bid;
執行計劃:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=23645.42..23647.95 rows=10 width=12) (actual time=128.606..130.502 rows=8 loops=1)
Group Key: pgbench_accounts.bid
-> Gather Merge (cost=23645.42..23647.75 rows=20 width=12) (actual time=128.598..130.490 rows=24 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=22645.39..22645.42 rows=10 width=12) (actual time=124.960..124.963 rows=8 loops=3)
Sort Key: pgbench_accounts.bid
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=22645.13..22645.23 rows=10 width=12) (actual time=124.917..124.920 rows=8 loops=3)
Group Key: pgbench_accounts.bid
-> Parallel Seq Scan on pgbench_accounts (cost=1.13..21603.46 rows=208333 width=8) (actual time=0.078..83.134 rows=266667 loops=3)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 66667
SubPlan 1
-> Seq Scan on pgbench_branches (cost=0.00..1.12 rows=1 width=4) (actual time=0.020..0.021 rows=2 loops=3)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.310 ms
Execution Time: 130.620 ms
(21 rows)
postgres=#
2.使用>ALL
SELECT
count( aid ),bid
FROM
pgbench_accounts
WHERE
bid > ALL ( SELECT bid FROM pgbench_branches WHERE bbalance > 0 )
GROUP BY
bid;
執行計劃:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=259581.79..259584.32 rows=10 width=12) (actual time=418.220..419.913 rows=8 loops=1)
Group Key: pgbench_accounts.bid
-> Gather Merge (cost=259581.79..259584.12 rows=20 width=12) (actual time=418.212..419.902 rows=24 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=258581.76..258581.79 rows=10 width=12) (actual time=413.906..413.909 rows=8 loops=3)
Sort Key: pgbench_accounts.bid
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=258581.50..258581.60 rows=10 width=12) (actual time=413.872..413.875 rows=8 loops=3)
Group Key: pgbench_accounts.bid
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..257539.83 rows=208333 width=8) (actual time=0.054..367.244 rows=266667 loops=3)
Filter: (SubPlan 1)
Rows Removed by Filter: 66667
SubPlan 1
-> Materialize (cost=0.00..1.13 rows=1 width=4) (actual time=0.000..0.001 rows=2 loops=1000000)
-> Seq Scan on pgbench_branches (cost=0.00..1.12 rows=1 width=4) (actual time=0.001..0.001 rows=2 loops=337880)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.218 ms
Execution Time: 420.035 ms
(22 rows)
postgres=#
3.使用NOT EXISTS
SELECT
count( aid ),bid
FROM
pgbench_accounts
WHERE
NOT EXISTS ( SELECT bid FROM pgbench_branches WHERE bbalance > 0 AND pgbench_accounts.bid = pgbench_branches.bid )
GROUP BY
bid;
執行計劃:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=28327.72..28330.25 rows=10 width=12) (actual time=152.024..153.931 rows=8 loops=1)
Group Key: pgbench_accounts.bid
-> Gather Merge (cost=28327.72..28330.05 rows=20 width=12) (actual time=152.014..153.917 rows=24 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=27327.70..27327.72 rows=10 width=12) (actual time=147.782..147.786 rows=8 loops=3)
Sort Key: pgbench_accounts.bid
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=27327.43..27327.53 rows=10 width=12) (actual time=147.732..147.737 rows=8 loops=3)
Group Key: pgbench_accounts.bid
-> Hash Anti Join (cost=1.14..25452.43 rows=375000 width=8) (actual time=0.134..101.884 rows=266667 loops=3)
Hash Cond: (pgbench_accounts.bid = pgbench_branches.bid)
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..20560.67 rows=416667 width=8) (actual time=0.032..45.174 rows=333333 loops=3)
-> Hash (cost=1.12..1.12 rows=1 width=4) (actual time=0.036..0.037 rows=2 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pgbench_branches (cost=0.00..1.12 rows=1 width=4) (actual time=0.025..0.027 rows=2 loops=3)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.322 ms
Execution Time: 154.040 ms
(22 rows)
postgres=#
4.使用LEFT JOIN和IS NULL
SELECT
count( aid ),a.bid
FROM
pgbench_accounts a
LEFT JOIN pgbench_branches b ON a.bid = b.bid AND b.bbalance > 0
WHERE
b.bid IS NULL
GROUP BY
a.bid;
執行計劃:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=28327.72..28330.25 rows=10 width=12) (actual time=145.298..147.096 rows=8 loops=1)
Group Key: a.bid
-> Gather Merge (cost=28327.72..28330.05 rows=20 width=12) (actual time=145.288..147.083 rows=24 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=27327.70..27327.72 rows=10 width=12) (actual time=141.883..141.887 rows=8 loops=3)
Sort Key: a.bid
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=27327.43..27327.53 rows=10 width=12) (actual time=141.842..141.847 rows=8 loops=3)
Group Key: a.bid
-> Hash Anti Join (cost=1.14..25452.43 rows=375000 width=8) (actual time=0.087..99.535 rows=266667 loops=3)
Hash Cond: (a.bid = b.bid)
-> Parallel Seq Scan on pgbench_accounts a (cost=0.00..20560.67 rows=416667 width=8) (actual time=0.025..44.337 rows=333333 loops=3)
-> Hash (cost=1.12..1.12 rows=1 width=4) (actual time=0.026..0.027 rows=2 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pgbench_branches b (cost=0.00..1.12 rows=1 width=4) (actual time=0.019..0.020 rows=2 loops=3)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.231 ms
Execution Time: 147.180 ms
(22 rows)
postgres=#
NOT IN 和 > ALL生成執行計劃都包含了一個子查詢。他們是各自獨立的。
而NOT EXISTS和LEFT JOIN生成了相同的執行計劃。
這些hash連接(或hash anti join)是完成查詢要求的最靈活的方式。這也是推薦exists或join的原因。因此，推薦使用exists或join的經驗法則是有效的。
但是，我們繼續往下看! 即使有了子查詢執行計劃，NOT IN子句的執行時間也會更好?
是的。PostgreSQL做了出色的優化，PostgreSQL將子查詢計劃進行了hash處理。因此PostgreSQL對如何處理IN子句有了更好的理解，這是一種邏輯思維方式，因為很多人傾向于使用IN子句。子查詢返回的行很少，但即使子查詢返回幾百行，也會發生同樣的情況。
但是，如果子查詢返回大量行(幾十萬行)怎么辦?讓我們嘗試一個簡單的測試：
CREATE TABLE t1 AS
SELECT * FROM generate_series(0, 500000) id;
CREATE TABLE t2 AS
SELECT (random() * 4000000)::integer id
FROM generate_series(0, 4000000);
ANALYZE t1;
ANALYZE t2;
EXPLAIN SELECT id
FROM t1
WHERE id NOT IN (SELECT id FROM t2);
執行計劃：
QUERY PLAN
--------------------------------------------------------------------------------
Gather (cost=1000.00..15195064853.01 rows=250000 width=4)
Workers Planned: 1
-> Parallel Seq Scan on t1 (cost=0.00..15195038853.01 rows=147059 width=4)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..93326.01 rows=4000001 width=4)
-> Seq Scan on t2 (cost=0.00..57700.01 rows=4000001 width=4)
(7 rows)
postgres=#
這里，執行計劃將子查詢進行了物化。代價評估變成了15195038853.01。(PostgreSQL的默認設置，如果t2表的行低于100k，會將子查詢進行hash)。這樣就會嚴重影響性能。因此，對于那種子查詢返回的行數很少的場景，IN子句可以起到很好的作用。
其它注意點
有的！在我們用不同的方式寫查詢的時候，可能有數據類型的轉換。
比如，語句：
EXPLAIN ANALYZE SELECT * FROM emp WHERE gen = ANY(ARRAY['M', 'F']);
就會發生隱式的類型轉換：
Seq Scan on emp (cost=0.00..1.04 rows=2 width=43) (actual time=0.023..0.026 rows=3 loops=1)
Filter: ((gen)::text = ANY ('{M,F}'::text[]))
這里的(gen)::text就發生了類型轉換。如果在大表上，這種類型轉換的代價會很高，因此，PostgreSQL對IN子句做了更好的處理。
EXPLAIN ANALYZE SELECT * FROM emp WHERE gen IN ('M','F');
Seq Scan on emp (cost=0.00..1.04 rows=3 width=43) (actual time=0.030..0.034 rows=3 loops=1)
Filter: (gen = ANY ('{M,F}'::bpchar[]))
將IN子句轉換成了ANY子句，沒有對gen列進行類型轉換。而是將M\F轉成了bpchar(內部等價于char)
總結
簡單來說，exists和直接join表通常比較好。
很多情況下，PostgreSQL將IN子句換成被hash的子計劃。在一些特殊場景下，IN可以獲得更好的執行計劃。
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL利用遞歸優化求稀疏列唯一值的方法
- PostgreSQL刪除更新優化操作
- Postgresql排序與limit組合場景性能極限優化詳解
- postgresql通過索引優化查詢速度操作
- postgresql關于like%xxx%的優化操作
