postgresql - 从联接表中选择 max(id) 低效查询计划
问题描述
我有一个视图req_res,它连接了 2 个表 - 请求和响应内部加入了 requestId。请求具有主键 - ID 列。
当我查询时(查询 1):
select max(ID) from req_res where ID > 1000000 and ID < 2000000;
解释计划:hash join : Request.ID 的索引扫描和 Response.request_id 的顺序扫描
查询时长:30s
当我将边界降低到 900k(查询 2)时:
select max(ID) from req_res where ID > 1000000 and ID < 1900000;
计划:嵌套循环:Request.ID 的索引扫描和 Response.request_id 的仅索引扫描
查询时长:3s
当我使用第一个查询并禁用哈希连接时 -set enable_hashjoin=off;
我得到了合并连接计划。当我也禁用合并连接计划时,set enable_mergejoin=off;
我得到了嵌套循环,它在3 秒内完成(而不是使用哈希连接的 30 秒)。
请求表的大小约为 70 百万条记录。大多数请求都有对应的响应,但其中一些没有。
版本:PostgreSQL 10.10
req_res DDL:
CREATE OR REPLACE VIEW public.req_res
AS SELECT req.id,
res.req_id,
res.body::character varying(500),
res.time,
res.duration,
res.balance,
res.header::character varying(100),
res.created_at
FROM response res
JOIN request req ON req.req_id = res.req_id;
查询 1 计划:
Aggregate (cost=2834115.70..2834115.71 rows=1 width=8) (actual time=154709.729..154709.730 rows=1 loops=1)
Buffers: shared hit=467727 read=685320 dirtied=214, temp read=240773 written=239751
-> Hash Join (cost=2493060.64..2831172.33 rows=1177346 width=8) (actual time=143800.101..154147.080 rows=1198706 loops=1)
Hash Cond: (req.req_id = res.req_id)
Buffers: shared hit=467727 read=685320 dirtied=214, temp read=240773 written=239751
-> Append (cost=0.44..55619.59 rows=1177346 width=16) (actual time=0.957..2354.648 rows=1200001 loops=1)
Buffers: shared hit=438960 read=32014
-> Index Scan using "5_5_req_pkey" on _hyper_2_5_chunk rs (cost=0.44..19000.10 rows=399803 width=16) (actual time=0.956..546.231 rows=399999 loops=1)
Index Cond: ((id >= 49600001) AND (id <= 50800001))
Buffers: shared hit=178872 read=10742
-> Index Scan using "7_7_req_pkey" on _hyper_2_7_chunk rs_1 (cost=0.44..36619.50 rows=777543 width=16) (actual time=0.037..767.744 rows=800002 loops=1)
Index Cond: ((id >= 49600001) AND (id <= 50800001))
Buffers: shared hit=260088 read=21272
-> Hash (cost=1367864.98..1367864.98 rows=68583298 width=8) (actual time=143681.850..143681.850 rows=68568554 loops=1)
Buckets: 262144 Batches: 512 Memory Usage: 7278kB
Buffers: shared hit=28764 read=653306 dirtied=214, temp written=233652
-> Append (cost=0.00..1367864.98 rows=68583298 width=8) (actual time=0.311..99590.021 rows=68568554 loops=1)
Buffers: shared hit=28764 read=653306 dirtied=214
-> Seq Scan on _hyper_3_2_chunk wt (cost=0.00..493704.44 rows=24941244 width=8) (actual time=0.309..14484.420 rows=24950147 loops=1)
Buffers: shared hit=661 read=243631
-> Seq Scan on _hyper_3_6_chunk wt_1 (cost=0.00..503935.04 rows=24978804 width=8) (actual time=0.334..14487.931 rows=24963020 loops=1)
Buffers: shared hit=168 read=253979
-> Seq Scan on _hyper_3_8_chunk wt_2 (cost=0.00..370225.50 rows=18663250 width=8) (actual time=0.327..10837.291 rows=18655387 loops=1)
Buffers: shared hit=27935 read=155696 dirtied=214
Planning time: 3.986 ms
Execution time: 154709.859 ms
查询 2 计划:
Finalize Aggregate (cost=2634042.50..2634042.51 rows=1 width=8) (actual time=5525.626..5525.627 rows=1 loops=1)
Buffers: shared hit=8764620 read=12779
-> Gather (cost=2634042.29..2634042.50 rows=2 width=8) (actual time=5525.609..5525.705 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=8764620 read=12779
-> Partial Aggregate (cost=2633042.29..2633042.30 rows=1 width=8) (actual time=5515.507..5515.508 rows=1 loops=3)
Buffers: shared hit=8764620 read=12779
-> Nested Loop (cost=0.88..2632023.83 rows=407382 width=8) (actual time=5.383..5261.979 rows=332978 loops=3)
Buffers: shared hit=8764620 read=12779
-> Append (cost=0.44..40514.98 rows=407383 width=16) (actual time=0.035..924.498 rows=333334 loops=3)
Buffers: shared hit=446706
-> Parallel Index Scan using "5_5_req_pkey" on _hyper_2_5_chunk rs (cost=0.44..16667.91 rows=166585 width=16) (actual time=0.033..169.854 rows=133333 loops=3)
Index Cond: ((id >= 49600001) AND (id <= 50600001))
Buffers: shared hit=190175
-> Parallel Index Scan using "7_7_req_pkey" on _hyper_2_7_chunk rs_1 (cost=0.44..23847.07 rows=240798 width=16) (actual time=0.039..336.091 rows=200001 loops=3)
Index Cond: ((id >= 49600001) AND (id <= 50600001))
Buffers: shared hit=256531
-> Append (cost=0.44..6.33 rows=3 width=8) (actual time=0.011..0.011 rows=1 loops=1000001)
Buffers: shared hit=8317914 read=12779
-> Index Only Scan using "2_2_response_pkey" on _hyper_3_2_chunk wt (cost=0.44..2.11 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1000001)
Index Cond: (req_id = req.req_id)
Heap Fetches: 0
Buffers: shared hit=3000005
-> Index Only Scan using "6_6_response_pkey" on _hyper_3_6_chunk wt_1 (cost=0.44..2.11 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1000001)
Index Cond: (req_id = req.req_id)
Heap Fetches: 192906
Buffers: shared hit=3551440 read=7082
-> Index Only Scan using "8_8_response_pkey" on _hyper_3_8_chunk wt_2 (cost=0.44..2.10 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=443006)
Index Cond: (req_id = req.req_id)
Heap Fetches: 162913
Buffers: shared hit=1766469 read=5697
Planning time: 0.839 ms
Execution time: 5525.814 ms
解决方案
推荐阅读
- ffmpeg - 使用 FFMPEG 从视频中提取图像并插入数据库
- c# - 如何访问拆分文本文件中的单个字符串
- python - 如何根据 groupby+nlargest 结果过滤我的数据框?
- mysql - 当 select 语句中给出格式“dd-MM-YYYY”时按日期排序未按预期工作
- synchronization - Mpeg TS:视频/音频同步的工作原理
- php - 读取 csv 文件列并将它们写入新文件
- dart - 我如何在 Dart 中按价格排序对象?
- python - Python:您将如何解释包含 reset_index 和 shift 方法的代码
- powershell - 尝试从任务计划程序运行 powershell 脚本时出现安全错误
- ios - 快速拖放collectionViewCell