围观博主“吹牛”被啪啪打脸，你说该不该打？
digoal约 9 分钟DuckDB确实很好用, 听说阿里云很多云产品(包括云PostgreSQL、MySQL)等都在集成DuckDB的能力, 可关注他们家的公众号的后续报道.
进入今天的正题: 某人（我）被啪啪打脸了，还好是自己人的“还我漂漂拳”。
结果是好的：1、了解了DuckDB的设计原则以及什么时候会使用索引？2、Bug不到一天就得到了解决。
对鸭子感兴趣也欢迎关注本公众号，加入DuckDB用户组。
打脸从前几天的这篇文章说起 :
PostGIS霸主地位不保! DuckDB SPATIAL 400倍性能傲视群雄
DuckDB Spatial 傲视群雄？
不可能，绝对不可能: DuckDB spatial 生产级还有待观察
PostGIS专家梅老师提到spatial join里的示例不够典型, 大量位置为同一个点, 性能提升打擦边球了.（梅老师是GIS老司机，以后大家有问题可以来群里找他）
DuckDB ST_DWithin和ST_Intersects等价用法的结果不一致, 说明DuckDB spatial插件还不能用于生产.
详见：
PostGIS霸主地位不保？PostGIS vs DuckDB空间分析性能实测
GIS空间分析场景DuckDB Spatial真比PostGIS快？
梅老师测试数据集( 可到这里找: https://github.com/digoal/blog/blob/master/202508/20250811_01.md ):
gis data: 20250811_01_data_001.zip
我在duckdb-spatial项目中也提了该 issue, 期待新的进展: https://github.com/duckdb/duckdb-spatial/issues/660
目前使用最新的spatial插件修复了: （814更新，已修复了，鸭子速度太快了）
FORCE INSTALL spatial FROM core_nightly;
问题细节 复现
准备数据, 数据文件来自以上gis data: 20250811_01_data_001.zip. 请自行替换为正确路径.
$ duckdb
DuckDB v1.3.2 (Ossivalis) 0b83e5d2f6
Enter ".help"for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
--开启时间测量
.timer on
-- 加载空间扩展
load spatial;
-- 建表与数据导入，构建rtree索引
-- 导入降水
CREATE TABLE pre(
id integer primary key,
geom geometry
);
CREATE SEQUENCE seq_pre_id START 1;
insert into pre(id,geom) select nextval('seq_pre_id'),geom from st_read('/Users/digoal/Downloads/20250811_01_data_001/pre.shp');
create index pre_geom_idx on pre using rtree(geom);
-- 导入温度数据
CREATE TABLE tem(
id integer primary key,
geom geometry
);
CREATE SEQUENCE seq_tem_id START 1;
insert into tem(id,geom) select nextval('seq_tem_id'),geom from st_read('/Users/digoal/Downloads/20250811_01_data_001/tem.shp');
create index tem_geom_idx on tem using rtree(geom);
-- 导入点测试数据
CREATE TABLE grid(
id integer primary key,
geom geometry
);
CREATE SEQUENCE seq_grid_id START 1;
insert into grid(id,geom) select nextval('seq_grid_id'),geom from st_read('/Users/digoal/Downloads/20250811_01_data_001/grid.shp');
create index grid_geom_idx on grid using rtree(geom);
叠加分析, 生成中间表tem_pre
-- 建表
create table tem_pre(
tem_id integer not null,
pre_id integer not null,
geom geometry
);
-- 索引
create index tem_pre_geom_idx on tem_pre using rtree(geom);
-- 分级结果数据灌入
insert into tem_pre(tem_id,pre_id,geom)
select a.id,b.id,ST_Intersection(a.geom,b.geom) from
tem a join pre b on (ST_Intersects(a.geom,b.geom));
-- 耗时
Run Time (s): real 1.406 user 1.393894 sys 0.012773
-- 交集数量
select count(*) from tem_pre;
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 167 │
└──────────────┘
降水温度交集与点数据集叠加分析, 这个请求性能反转了, DuckDB不如PostGIS
create table tem_pre_grid(
tem_id integer not null,
pre_id integer not null,
grid_id integer not null,
);
-- 插入数据
insert into tem_pre_grid(tem_id,pre_id,grid_id)
select a.tem_id,a.pre_id,b.id as grid_id from
tem_pre a join grid b on (ST_Intersects(a.geom,b.geom));
PostGIS仅需1秒, DuckDB为什么需要几秒?
我后面有提到, DuckDB不管怎么配置强制索引还是关闭spatial_join优化, 它都不走nl join(外表小表 + 内表大表的rtree索引扫描).
结果正确性测试, 目前DuckDB st_dwithin和st_intersects测试结果不一致, 不符合预期
-- ST_Intersects(a.geom,b.geom) 等价于 ST_DWithin(a.geom,b.geom,0)
-- 理论上结果应该一致
select a.tem_id,a.pre_id,b.id as grid_id from
tem_pre a join grid b on (ST_Intersects(a.geom,b.geom));
select a.tem_id,a.pre_id,b.id as grid_id from
tem_pre a join grid b on (ST_DWithin(a.geom,b.geom,0));
好了, 下面再来看看为什么无论如何都无法让DuckDB走rtree扫描以及nl join.
关于这个问题和deepwiki交互了几轮, 可参考:
https://deepwiki.com/search/duckdbindex-scan_14c98f92-a577-4c96-adf5-9d600eeaeaa1
测试如下
SET index_scan_max_count = 1;
SET index_scan_percentage = 0.000001; -- 0.01%
SET disabled_optimizers = 'extension';
explain select a.tem_id,a.pre_id,b.id as grid_id from
tem_pre a join grid b on (ST_DWithin(a.geom,b.geom,0));
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ tem_id │
│ pre_id │
│ grid_id │
│ │
│ ~512260 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ BLOCKWISE_NL_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ ├──────────────┐
│ Condition: │ │
│ ST_DWithin(geom, geom) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Table: grid ││ Table: tem_pre │
│ Type: Sequential Scan ││ Type: Sequential Scan │
│ ││ │
│ Projections: ││ Projections: │
│ geom ││ geom │
│ id ││ tem_id │
│ ││ pre_id │
│ ││ │
│ ~512260 Rows ││ ~167 Rows │
└───────────────────────────┘└───────────────────────────┘
reset disabled_optimizers;
explain select a.tem_id,a.pre_id,b.id as grid_id from
tem_pre a join grid b on (ST_DWithin(a.geom,b.geom,0));
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ tem_id │
│ pre_id │
│ grid_id │
│ │
│ ~512260 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SPATIAL_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ │
│ Conditions: ├──────────────┐
│ ST_DWithin(geom, geom) │ │
│ │ │
│ ~512260 Rows │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Table: grid ││ Table: tem_pre │
│ Type: Sequential Scan ││ Type: Sequential Scan │
│ ││ │
│ Projections: ││ Projections: │
│ geom ││ geom │
│ id ││ tem_id │
│ ││ pre_id │
│ ││ │
│ ~512260 Rows ││ ~167 Rows │
└───────────────────────────┘└───────────────────────────┘
即使是简单的int类型的大表和非常小的表JOIN, 也不走索引. 当然速度也确实不差, 似乎DuckDB对hash join有执念.
create table a (id int, info text);
create table b (id int, info text);
insert into a select id, md5(random()::text) from range(0,10000000) as t(id);
insert into b select id, md5(random()::text) from range(0,10) as t(id);
create index idxa_id on a (id);
create index idxb_id on b (id);
select a.*,b.* from a join b on (a.id=b.id);
set nested_loop_join_threshold=1000000000;
D select a.*,b.* from a join b on (a.id=b.id);
┌───────┬──────────────────────────────────┬───────┬──────────────────────────────────┐
│ id │ info │ id │ info │
│ int32 │ varchar │ int32 │ varchar │
├───────┼──────────────────────────────────┼───────┼──────────────────────────────────┤
│ 0 │ 9396f672100e2f95d767fa7edba5db17 │ 0 │ 8aa10a44ff84808317cba2cd50e53251 │
│ 1 │ 31bf2b0d8c5c51c113e1f68e4d5f3a9b │ 1 │ 6612d1c13d1e06f225715ea3c0e93872 │
│ 2 │ aa0f1a0c0859301617568bb33e188406 │ 2 │ 69744d8a3316321485e3dfdd72eff8bb │
│ 3 │ 1453f39c00106da23a9b57f3c7f8fbb8 │ 3 │ 8f6193f05c52ceac7f220d72ef592b73 │
│ 4 │ 402b581d5edd0f526920d191190f6d6a │ 4 │ b1372f5837eaf74b80076c2ff9dd7b36 │
│ 5 │ f64bc93932bd8bad3f7cdf76ba3d4f9d │ 5 │ 6ccde11f831a74dfff82f0d66af2ea9b │
│ 6 │ 02a60e1226d81736f38135e9e2e98270 │ 6 │ ca760054cbfa462035ffe46fffbd9c13 │
│ 7 │ bf61fbf7a5d751339dead9cafcb5ca7a │ 7 │ f3927b97e91d19516cf6899140d9ec9c │
│ 8 │ f0036eb0ad1ed1961f1a057ab7aa035c │ 8 │ b6fdae15de4dd3d66625257373a5fb54 │
│ 9 │ dcc477fdf26dc638891def7a666e620b │ 9 │ a67e85e109f76e7467a4cff1d6f7b58a │
├───────┴──────────────────────────────────┴───────┴──────────────────────────────────┤
│ 10 rows 4 columns │
└─────────────────────────────────────────────────────────────────────────────────────┘
Run Time (s): real 0.002 user 0.002147 sys 0.000437
D explain select a.*,b.* from a join b on (a.id=b.id);
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ id │
│ info │
│ id │
│ info │
│ │
│ ~10 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ Conditions: id = id ├──────────────┐
│ │ │
│ ~10 Rows │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ SEQ_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ Table: a ││ Table: b │
│ Type: Sequential Scan ││ Type: Sequential Scan │
│ ││ │
│ Projections: ││ Projections: │
│ id ││ id │
│ info ││ info │
│ ││ │
│ Filters: id<=9 ││ │
│ ││ │
│ ~10000000 Rows ││ ~10 Rows │
└───────────────────────────┘└───────────────────────────┘
Run Time (s): real 0.002 user 0.001576 sys 0.000117
deepwiki对DuckDB不使用NL JOIN的解释
您询问的是关于DuckDB中连接操作选择的问题。根据您的查询，您想了解为什么在外表（表b，10行）为小表，内表（表a，1000万行）为大表且有索引的情况下，系统不选择nested loop join。
DuckDB的连接算法选择逻辑
DuckDB在 PlanComparisonJoin 函数中决定使用哪种连接算法 。
对于您的查询场景，系统会按以下优先级选择连接算法：
1. 优先选择Hash Join
由于您的连接条件是等值连接（a.id=b.id），系统首先会考虑hash join 。Hash join通常比nested loop join更高效，特别是对于大数据集。
2. Nested Loop Join的限制条件
系统只有在满足特定条件时才会选择nested loop join ：
左表或右表的估计基数必须小于等于 nested_loop_join_threshold（默认值为5）
在您的案例中：
表a有1000万行，远超阈值 表b有10行，虽然小于阈值，但系统仍会优先选择hash join
3. 为什么不选择Nested Loop Join
即使表b很小（10行），DuckDB仍然倾向于使用hash join，原因包括：
算法复杂度：Hash join的时间复杂度是O(M+N)，而nested loop join是O(M×N) 索引无关性：DuckDB的连接算法选择主要基于表的大小，而不是索引的存在 向量化执行：Hash join更适合DuckDB的向量化执行模型
4. 连接算法的完整选择逻辑
系统按以下顺序评估连接算法 ：
IE Join（如果支持范围条件） Piecewise Merge Join（如果支持） Nested Loop Join（如果支持且满足阈值条件） Blockwise NL Join（作为后备选项）
Notes
DuckDB的连接算法选择是基于成本的优化决策，主要考虑数据量而非索引存在性。在您的场景中，虽然表b很小，但由于表a很大，hash join仍然是更优的选择。如果您想强制使用nested loop join，可以调整 nested_loop_join_threshold 配置参数，但这通常不推荐，因为可能导致性能下降。
问题继续：您的问题是关于即使设置了 nested_loop_join_threshold 大于表a和表b的记录数，DuckDB仍然选择hash join而不是nested loop join的原因。
让我分析一下DuckDB中连接算法选择的完整逻辑：
连接算法选择的优先级
在 PlanComparisonJoin 函数中，DuckDB按以下优先级选择连接算法：
关键发现：等值连接优先选择Hash Join
即使您增加了 nested_loop_join_threshold，系统仍然会优先选择hash join，因为：
等值连接的优先级：当查询包含等值连接条件（如
a.id=b.id）时，系统会直接创建PhysicalHashJoin，而不会进入后续的nested loop join判断逻辑Nested Loop Join的检查时机：只有在以下情况下才会考虑nested loop join：
查询不包含等值连接条件，或者 设置了 prefer_range_joins且满足范围连接条件
为什么调整阈值无效
nested_loop_join_threshold 的检查发生在等值连接判断之后：
这意味着对于您的等值连接查询 a.id=b.id，系统在检查阈值之前就已经选择了hash join。
默认配置值
nested_loop_join_threshold 的默认值确实是5：
解决方案
要强制使用nested loop join，您需要：
使用非等值连接条件，或者 修改查询结构，避免等值连接的直接匹配
Notes
DuckDB的连接算法选择逻辑中，等值连接具有最高优先级，会直接选择hash join而绕过基数阈值检查。这是一个设计决策，因为hash join在大多数等值连接场景下都比nested loop join更高效。
就说这么多吧, 反正脸都被打肿(漂亮)了.
但是我依旧坚持：一定要关注spatial的进展, 毕竟它是DuckDB!