SEMI-JOIN是什么鬼?性能提升921倍?
digoal约 12 分钟文章开始前推荐2个学习环境:
1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》
2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》
背景
当参与等值JOIN的表达式存在有重复值时, 如果不需要找出该表其他字段的值(也就是仅使用JOIN字段/表达式), 那么JOIN时只需要查每个值的第一条, 然后就可以跳到下一个值. 在数据库中常常被用来优化 in, exists, not exists, = any(), except 等操作(或者逻辑上成立的其他JOIN场景).
原理可参考: 《PostgreSQL 与关系代数 (Equi-Join , Semi-Join , Anti-Join , Division)》
例如 Oracle中的半连接 hint: https://cloud.tencent.com/developer/article/1515841
并不是所有数据库都实现了所有场景的semi join, 如果未实现, 有什么方法可以模拟semi-join?
答案是: 递归/group by/distinct on/distinct
PostgreSQL 例子
准备测试数据
postgres=# create table a (id int, info text, ts timestamp);
CREATE TABLE
postgres=# create table b (like a);
CREATE TABLE
postgres=# insert into a select id, md5(random()::text), now() from generate_series(0,1000000) as t(id);
INSERT 0 1000001 -- b表的100万行记录中b.id只有11个唯一值
postgres=# insert into b select random()*10, md5(random()::text), now() from generate_series(0,1000000) as t(id);
INSERT 0 1000001
postgres=# create index on a (id);
CREATE INDEX
postgres=# create index on b (id);
CREATE INDEX
未优化SQL
select a.* from a where exists (select 1 from b where a.id=b.id); postgres=# explain analyze select a.* from a where exists (select 1 from b where a.id=b.id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=18436.17..18436.66 rows=11 width=45) (actual time=226.590..226.598 rows=11 loops=1)
Merge Cond: (a.id = b.id)
-> Index Scan using a_id_idx on a (cost=0.42..27366.04 rows=1000001 width=45) (actual time=0.010..0.013 rows=12 loops=1)
-> Sort (cost=18435.74..18435.77 rows=11 width=4) (actual time=226.576..226.577 rows=11 loops=1)
Sort Key: b.id
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=18435.44..18435.55 rows=11 width=4) (actual time=226.568..226.570 rows=11 loops=1)
Group Key: b.id
Batches: 1 Memory Usage: 24kB
-> Index Only Scan using b_id_idx on b (cost=0.42..15935.44 rows=1000001 width=4) (actual time=0.010..77.936 rows=1000001 loops=1)
Heap Fetches: 0
Planning Time: 0.189 ms
Execution Time: 226.630 ms
(13 rows)
以上查询没有使用semi-join, 性能很一般.
由于b表的100万行记录中b.id只有11个唯一值, 可以使用semi-join进行加速.
用法参考: 《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》
使用递归模拟SEMI-JOIN, 只需要 0.171 ms 既可得出b表 11个值的结果.
with recursive tmp as (
select min(id) as id from b
union all
select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null; id
----
0
1
2
3
4
5
6
7
8
9
10
(11 rows)
执行计划如下
postgres=# explain analyze with recursive tmp as (
select min(id) as id from b
union all
select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
CTE Scan on tmp (cost=50.07..52.09 rows=100 width=4) (actual time=0.028..0.134 rows=11 loops=1)
Filter: (id IS NOT NULL)
Rows Removed by Filter: 1
CTE tmp
-> Recursive Union (cost=0.44..50.07 rows=101 width=4) (actual time=0.025..0.126 rows=12 loops=1)
-> Result (cost=0.44..0.45 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)
InitPlan 3 (returns $1)
-> Limit (cost=0.42..0.44 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)
-> Index Only Scan using b_id_idx on b b_1 (cost=0.42..18435.44 rows=1000001 width=4) (actual time=0.020..0.020 rows=1 loops=1)
Index Cond: (id IS NOT NULL)
Heap Fetches: 0
-> WorkTable Scan on tmp tmp_1 (cost=0.00..4.76 rows=10 width=4) (actual time=0.007..0.007 rows=1 loops=12)
Filter: (id IS NOT NULL)
Rows Removed by Filter: 0
SubPlan 2
-> Result (cost=0.45..0.46 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=11)
InitPlan 1 (returns $3)
-> Limit (cost=0.42..0.45 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=11)
-> Index Only Scan using b_id_idx on b (cost=0.42..6979.51 rows=333334 width=4) (actual time=0.006..0.006 rows=1 loops=11)
Index Cond: ((id IS NOT NULL) AND (id > tmp_1.id))
Heap Fetches: 0
Planning Time: 0.177 ms
Execution Time: 0.171 ms
(23 rows)
使用递归模拟semi-join, SQL改写如下:
select a.* from a where exists (select 1 from b where a.id=b.id); 改写成
select a.* from a where exists (select 1 from
(
with recursive tmp as (
select min(id) as id from b
union all
select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null
) b
where a.id=b.id);
改写后速度从226.630 ms 提升到 0.246 ms
postgres=# explain analyze select a.* from a where exists (select 1 from
(
with recursive tmp as (
select min(id) as id from b
union all
select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null
) b
where a.id=b.id);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=53.76..318.49 rows=100 width=45) (actual time=0.154..0.189 rows=11 loops=1)
-> HashAggregate (cost=53.34..54.34 rows=100 width=4) (actual time=0.144..0.149 rows=11 loops=1)
Group Key: tmp.id
Batches: 1 Memory Usage: 24kB
-> CTE Scan on tmp (cost=50.07..52.09 rows=100 width=4) (actual time=0.027..0.139 rows=11 loops=1)
Filter: (id IS NOT NULL)
Rows Removed by Filter: 1
CTE tmp
-> Recursive Union (cost=0.44..50.07 rows=101 width=4) (actual time=0.024..0.130 rows=12 loops=1)
-> Result (cost=0.44..0.45 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)
InitPlan 3 (returns $1)
-> Limit (cost=0.42..0.44 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)
-> Index Only Scan using b_id_idx on b b_1 (cost=0.42..18435.44 rows=1000001 width=4) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: (id IS NOT NULL)
Heap Fetches: 0
-> WorkTable Scan on tmp tmp_1 (cost=0.00..4.76 rows=10 width=4) (actual time=0.008..0.008 rows=1 loops=12)
Filter: (id IS NOT NULL)
Rows Removed by Filter: 0
SubPlan 2
-> Result (cost=0.45..0.46 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=11)
InitPlan 1 (returns $3)
-> Limit (cost=0.42..0.45 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=11)
-> Index Only Scan using b_id_idx on b (cost=0.42..6979.51 rows=333334 width=4) (actual time=0.006..0.006 rows=1 loops=11)
Index Cond: ((id IS NOT NULL) AND (id > tmp_1.id))
Heap Fetches: 0
-> Index Scan using a_id_idx on a (cost=0.42..2.63 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=11)
Index Cond: (id = tmp.id)
Planning Time: 0.295 ms
Execution Time: 0.246 ms
(29 rows)
DuckDB 例子
DuckDB已支持semi-join
postgres@56000550f873:~$ ./duckdb
v0.9.2 3c695d7ba9
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D create table a (id int, info text, ts timestamp);
D create table b (id int, info text, ts timestamp);
D insert into a select id, md5(random()::text), now() from range(0,1000000) as t(id);
D insert into b select random()*10, md5(random()::text), now() from range(0,1000000) as t(id);
D create index idxa on a (id);
D create index idxb on b (id);
但是从执行计划可以看出, DuckDB这里没有使用index skip scan:
D explain analyze select a.* from a where exists (select 1 from b where a.id=b.id); explain analyze select a.* from a where exists (select 1 from b where a.id=b.id);
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Total Time: 0.0484s ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│ EXPLAIN_ANALYZE │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ 0 │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ SEMI │
│ id IS NOT DISTINCT FROM id│
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ├──────────────┐
│ EC: 1000000 │ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │
│ 11 │ │
│ (0.04s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ SEQ_SCAN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ a ││ b │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ id ││ id │
│ info ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ ts ││ EC: 1000000 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 1000000 ││ 1000000 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ (0.00s) │
│ 1000000 ││ │
│ (0.02s) ││ │
└───────────────────────────┘└───────────────────────────┘
Run Time (s): real 0.051 user 0.117643 sys 0.018274
虽然duckdb也支持递归, 但是DuckDB对递归的优化不太友好, 需要0.064秒, 可能duckdb的优化器更适合大批量数据计算.
D with recursive tmp as (
> select min(id) as id from b
> union all
> select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null
> )
> select * from tmp where tmp.id is not null;
┌───────┐
│ id │
│ int32 │
├───────┤
│ 0 │
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
│ 10 │
└───────┘
Run Time (s): real 0.064 user 0.240191 sys 0.035479 D select a.* from a where exists (select 1 from
> (
> with recursive tmp as (
> select min(id) as id from b
> union all
> select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null
> )
> select * from tmp where tmp.id is not null
> ) b
> where a.id=b.id);
┌───────┬──────────────────────────────────┬───────────────────────┐
│ id │ info │ ts │
│ int32 │ varchar │ timestamp │
├───────┼──────────────────────────────────┼───────────────────────┤
│ 0 │ de1ef3eadbc5d3a8fe10fd9c9e4c90d2 │ 2024-01-03 11:32:35.1 │
│ 1 │ 0078e0550a696772b8d21744cf6ada88 │ 2024-01-03 11:32:35.1 │
│ 2 │ a42b9b4a0c77a90d7a4b0b0f71a99564 │ 2024-01-03 11:32:35.1 │
│ 3 │ 70a0af28f15818f2d72aeb06809383f2 │ 2024-01-03 11:32:35.1 │
│ 4 │ a805d9906eac73fb348834bb56444d37 │ 2024-01-03 11:32:35.1 │
│ 5 │ eb11674a546980a3364aeaeb16206fc6 │ 2024-01-03 11:32:35.1 │
│ 6 │ 9335fd3c688851e55fc5039b7d26e934 │ 2024-01-03 11:32:35.1 │
│ 7 │ a51ce7b9203f0858e3e56779167a1012 │ 2024-01-03 11:32:35.1 │
│ 8 │ 0a723d0d3e07a26fb134bab02b78533d │ 2024-01-03 11:32:35.1 │
│ 9 │ 76cbfc8316371fa740cae231563e212b │ 2024-01-03 11:32:35.1 │
│ 10 │ 710bc959af73686d5941d5e2ddb1e0cf │ 2024-01-03 11:32:35.1 │
├───────┴──────────────────────────────────┴───────────────────────┤
│ 11 rows 3 columns │
└──────────────────────────────────────────────────────────────────┘
Run Time (s): real 0.073 user 0.287287 sys 0.033601
explain analyze select a.* from a where exists (select 1 from
(
with recursive tmp as (
select min(id) as id from b
union all
select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null
)
select * from tmp where tmp.id is not null
) b
where a.id=b.id);
explain analyze select a.* from a where exists (select 1 from ( with recursive tmp as ( select min(id) as id from b union all select (select min(b.id) from b where b.id > tmp.id) from tmp where tmp.id is not null ) select * from tmp where tmp.id is not null ) b where a.id=b.id);
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Total Time: 0.0702s ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
┌───────────────────────────┐
│ EXPLAIN_ANALYZE │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ 0 │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ SEMI │
│ id IS NOT DISTINCT FROM id│
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ├──────────────┐
│ EC: 1000000 │ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │
│ 11 │ │
│ (0.01s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ FILTER │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ a ││ (id IS NOT NULL) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ id ││ EC: 1000000 │
│ info ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ ts ││ 11 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ (0.00s) │
│ EC: 1000000 ││ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ │
│ 1000000 ││ │
│ (0.01s) ││ │
└───────────────────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ FILTER │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ (id IS NOT NULL) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 1000000 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ 11 │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ REC_CTE │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ tmp │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ idx: 7 ├──────────────┐
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │
│ 12 │ │
│ (0.06s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ UNGROUPED_AGGREGATE ││ PROJECTION │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ min(#0) ││ SUBQUERY │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ 1 ││ 11 │
│ (0.00s) ││ (0.00s) │
└─────────────┬─────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ PROJECTION ││ DELIM_JOIN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ id ││ SINGLE │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ id IS NOT DISTINCT FROM id│
│ 1000000 ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ├──────────────┐───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ (0.00s) ││ EC: 1000000 │ │ │
│ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ │
│ ││ 0 │ │ │
│ ││ (0.00s) │ │ │
└─────────────┬─────────────┘└─────────────┬─────────────┘ │ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ SEQ_SCAN ││ FILTER ││ HASH_JOIN │ │ HASH_GROUP_BY │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ b ││ (id IS NOT NULL) ││ SINGLE │ │ #0 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ id IS NOT DISTINCT FROM id│ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ id ││ EC: 0 ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ 22 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ EC: 1000000 ├──────────────┐ │ (0.00s) │
│ EC: 1000000 ││ 11 ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ │ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ (0.00s) ││ 11 │ │ │ │
│ 1000000 ││ ││ (0.00s) │ │ │ │
│ (0.00s) ││ ││ │ │ │ │
└───────────────────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘ │ └───────────────────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ REC_CTE_SCAN ││ COLUMN_DATA_SCAN ││ PROJECTION │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ idx: 7 ││ 11 ││ min(id) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ (0.00s) ││ id │
│ 12 ││ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ (0.00s) ││ ││ 10 │
│ ││ ││ (0.00s) │
└───────────────────────────┘└───────────────────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ INNER │
│ #0 IS NOT DISTINCT FROM id│
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ├───────────────────────────────────────────┐
│ EC: 1000000 │ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │
│ 10 │ │
│ (0.00s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐ ┌─────────────┴─────────────┐
│ HASH_GROUP_BY │ │ DELIM_SCAN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ #0 │ │ 0 │
│ min(#1) │ │ (0.00s) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ │
│ 10 │ │ │
│ (0.09s) │ │ │
└─────────────┬─────────────┘ └───────────────────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ id │
│ id │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ 4997665 │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ NESTED_LOOP_JOIN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ INNER │
│ id > id │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ├──────────────┐
│ EC: 1000000 │ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │
│ 4997665 │ │
│ (0.06s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ DELIM_SCAN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ b ││ 0 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ (0.00s) │
│ id ││ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ │
│ EC: 1000000 ││ │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ │
│ 11012288 ││ │
│ (0.01s) ││ │
└───────────────────────────┘└───────────────────────────┘
Run Time (s): real 0.076 user 0.275786 sys 0.034828
参考
《PostgreSQL 14 preview - 大表search IN ( consts ) - linear search TO hash table probe (consts 个数>= MIN_ARRAY_SIZE_FOR_HASHED_SAOP)》
《PostgreSQL 16 preview - 支持 Right Anti Join , 对hash right join (non-nullable input)可选择small table作为hash table.》
《PostgreSQL 与关系代数 (Equi-Join , Semi-Join , Anti-Join , Division)》
Oracle中的半连接 hint: https://cloud.tencent.com/developer/article/1515841
《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》
《PostgreSQL DISTINCT 和 DISTINCT ON 语法的使用》
《DB吐槽大会,第62期 - PG 不支持index skip scan》
《SQLite3 的index skip scan优化器功能》
欢迎来参加1.6号上海举办的《国产数据库共话未来趋势》-数据库实践哪家强的沙龙, 现场和大师畅聊技术与人生, 奖品拿到手软, 提前祝大家春节快乐!
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.