物联网全表扫描查询卡死、超时？别乱建索引了！PostgreSQL 18 跳跃扫描，解决多年运维顽疾
作者： ShunWah
公众号： "shunwah星辰数智社"主理人。持有认证： OceanBase、MySQL OCP、OpenGauss、崖山、金仓KingBase、KaiwuDB、 亚信AntDBCA、翰高、GBase、Galaxybase GBCA、Neo4j 、NebulaGraph、东方通TongTech、TiDB等多项权威认证。
获奖经历： 崖山YashanDB YVP、浪潮KaiwuDB MVP、墨天轮 MVP、金仓社区KVA、TiDB社区MVA、NebulaGraph社区之星、IFClub星珩联盟·智库星系技术专家、ITPUB 技术专家，社区版主及布道师。在OceanBase&墨天轮征文大赛、OpenGauss、TiDB、YashanDB、Kingbase、KWDB、Navicat 征文等赛事中多次斩获一、二、三等奖，原创技术文章常年被墨天轮、CSDN、ITPUB 等平台首页推荐。
- CSDN_ID： shunwahma
- 墨天轮_ID：shunwah
- ITPUB_ID： shunwah
- IFClub_ID：shunwah
? 前言
回顾 2025 年的数据库领域，海内外技术演进呈现出截然不同的节奏。海外市场在分布式、云原生、向量检索等赛道持续内卷，而国内则迎来了多模融合、国产化替代与物联网时序场景的深度磨合期。我关注的不只是新特性有多“炫”，更是它在海量设备数据写入、复杂查询压测下，能否真正 稳定、可控、可维护。
之前在论坛看到一个问题争论不休：物联网平台的数据存储，到底该选专用的时序数据库，还是回归通用的关系型数据库？有网友的结论很直接—— 优先考虑 PostgreSQL 兼容，才是最稳妥的长期策略。而 PostgreSQL 18 在 2025 年（模拟环境）带来的“跳跃扫描”（Skip Scan）特性，恰好为这个结论增添了有力注脚。
本文将从物联网运维视角出发，结合实际案例和命令行操作，重新理解这个看似不起眼却极为实用的优化特性。
1️⃣ 为什么物联网场景更应该拥抱 PostgreSQL 兼容？
1.1 物联网数据存储的三大痛点
在多年的平台运维中，我们几乎每天都要面对以下三个问题：
- ?
设备型号多样：不同厂商上报的数据字段差异大，导致表结构频繁变更或使用大量
JSONB字段。 - ? 查询模式不可控：既有按设备 ID + 时间戳的范围扫描，也有仅按某业务属性（如区域、告警等级）的过滤查询。
- ?️ 运维技能栈分散：团队既要维护时序库，又要维护关系库，还要处理各类异构数据同步，故障定位困难。
专用时序数据库（如 InfluxDB、TDengine）在写入性能和压缩比上确实优秀，但一旦遇到复杂关联查询（例如“查询某区域下所有设备最近 1 小时的平均温度，并关联设备元数据表”），往往需要额外开发或引入中间件，运维复杂度陡增。
1.2 PostgreSQL 生态：物联网的“稳定三角”
PostgreSQL 本身并不是为时序场景设计的，但经过多年的扩展生态发展（TimescaleDB、pg_partman、columnar 等），它已经形成了一套 通用存储 + 时序扩展 + 丰富索引的组合方案。而选择 PostgreSQL 兼容（无论是原生 PG、华为 openGauss，还是崖山 YashanDB、金仓 KingBase 等），带来的收益非常明确：
| 维度 | 专用时序数据库 | PostgreSQL 兼容方案 |
|---|---|---|
| ?️ SQL 生态 | 类 SQL 或专用语法 | 完整 SQL 支持，PL/pgSQL 存储过程 |
| ? 关联查询能力 | 弱（通常需预聚合或外部 JOIN） | 强（与关系表无缝 JOIN） |
| ? 运维工具链 | 相对封闭 | pgAdmin、pg_dump、Patroni、主流监控全覆盖 |
| ? 学习迁移成本 | 高（新语法、新工具） | 低（DBA/运维已有经验复用） |
对于物联网行业而言， “稳妥”不等于保守，而是在设备数量从 10 万增长到 100 万的过程中，确保每一类查询都有可预测的执行计划，每一个运维操作都有成熟的社区方案。
2️⃣ PostgreSQL 18 跳跃扫描：专治物联网“非前缀查询”
? 步骤 1：启动容器并登录数据库
[root@openeuler-server ~]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 91b8b62007f3 postgres:18-alpine "docker-entrypoint.s…" 29 hours ago Up 29 hours 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp pg18 [root@openeuler-server ~]# docker exec -it pg18 bash91b8b62007f3:/#
91b8b62007f3:/# psql -U postgres -d postgrespsql (18.3) Type "help" for help. postgres=#
? 步骤 2：切换到物联网业务库
postgres=# \c iot_platformYou are now connected to database "iot_platform" as user "postgres". iot_platform=#
2.1 一个让传统 B-Tree 索引头疼的场景
假设我们有一张物联网设备数据表：
CREATE TABLE device_metrics ( tenant_id INT, -- 租户ID（多租户隔离） device_id BIGINT, -- 设备ID metric_time TIMESTAMPTZ, -- 上报时间 temperature FLOAT, -- 温度值 alert_level SMALLINT -- 告警等级 0-5);
iot_platform=# CREATE TABLE device_metrics (iot_platform(# tenant_id INT,iot_platform(# device_id BIGINT,iot_platform(# metric_time TIMESTAMPTZ,iot_platform(# temperature FLOAT,iot_platform(# alert_level SMALLINTiot_platform(# );CREATE TABLEiot_platform=#
最常见的复合索引（前缀为 tenant_id）：
CREATE INDEX idx_tenant_time ON device_metrics (tenant_id, metric_time);
iot_platform=# CREATE INDEX idx_tenant_time ON device_metrics (tenant_id, metric_time);CREATE INDEXiot_platform=#
我们的运维同事经常会执行这样一类查询：“
找出某个租户下，最近发生过 3 级以上告警的所有设备的最新一条记录”。但实际上，更多时候业务侧会直接按
alert_level 过滤，而不带
tenant_id：
-- 查询所有租户中告警等级 >= 3 的最新 100 条记录SELECT * FROM device_metricsWHERE alert_level >= 3ORDER BY metric_time DESCLIMIT 10;
iot_platform=# SELECT * FROM device_metricsiot_platform-# WHERE alert_level >= 3iot_platform-# ORDER BY metric_time DESCiot_platform-# LIMIT 10; tenant_id | device_id | metric_time | temperature | alert_level -----------+-----------+-------------------------------+--------------------+------------- 95 | 5008 | 2026-04-23 09:28:33.252303+00 | 36.203448914018146 | 5 60 | 6411 | 2026-04-23 09:28:30.723324+00 | 25.817128932068496 | 3 21 | 1265 | 2026-04-23 09:28:27.421996+00 | 42.58459893462557 | 3 85 | 1654 | 2026-04-23 09:28:26.781733+00 | 23.877115521269292 | 4 57 | 6983 | 2026-04-23 09:28:25.616087+00 | 17.046910122423448 | 3 10 | 2122 | 2026-04-23 09:28:24.78328+00 | 35.09004518016836 | 5 52 | 4806 | 2026-04-23 09:28:23.013014+00 | 33.19417900521016 | 5 36 | 547 | 2026-04-23 09:28:21.040972+00 | 18.55870133962211 | 3 53 | 4586 | 2026-04-23 09:28:19.493695+00 | 18.48621493239935 | 5 71 | 8097 | 2026-04-23 09:28:17.456574+00 | 19.556401229930785 | 4 (10 rows) iot_platform=#
⚠️ 在 PostgreSQL 17 及更早版本中，由于复合索引
(tenant_id, metric_time)的前缀tenant_id未被查询条件引用，优化器会拒绝使用该索引，转而执行 全表扫描或仅依赖metric_time上的独立索引（如果存在）。当表数据量达到几十亿行时，这个查询可能耗时数十秒甚至超时。
3️⃣ 完整验证脚本（含数据生成与跳跃扫描验证）
? 步骤 1：生成测试数据
-- 建表
iot_platform=# CREATE TABLE device_metrics (iot_platform(# tenant_id INT,iot_platform(# device_id BIGINT,iot_platform(# metric_time TIMESTAMPTZ,iot_platform(# temperature FLOAT,iot_platform(# alert_level SMALLINTiot_platform(# );CREATE TABLEiot_platform=#
-- 创建复合索引
iot_platform=# CREATE INDEX idx_tenant_time ON device_metrics (tenant_id, metric_time);CREATE INDEXiot_platform=# CREATE INDEX idx_tenant_alert_time ON device_metrics (tenant_id, alert_level, metric_time);CREATE INDEXiot_platform=#
-- 插入测试数据（10万行）
iot_platform=# INSERT INTO device_metricsiot_platform-# SELECT iot_platform-# (random()*9)::int,iot_platform-# (random()*100)::bigint,iot_platform-# '2025-12-01 00:00:00'::timestamptz + (random()*30*24*3600) * interval '1 second',iot_platform-# random()*40,iot_platform-# (random()*5)::intiot_platform-# FROM generate_series(1, 100000);INSERT 0 100000iot_platform=#
-- 验证数据
iot_platform=# SELECT '总记录数' as 指标, COUNT(*)::text as 数值 FROM device_metrics iot_platform-# UNION ALLiot_platform-# SELECT '告警等级>=3', COUNT(*)::text FROM device_metrics WHERE alert_level >= 3; 指标 | 数值 -------------+-------- 告警等级>=3 | 49742 总记录数 | 100000 (2 rows) iot_platform=#
⚙️ 步骤 2：关于
enable_skip_scan 参数的说明
? 重要更正：跳跃扫描在 PostgreSQL 18 中是 默认启用的，并不需要手动设置开关。根据 PostgreSQL 官方文档，该优化由查询规划器基于成本估算自动决定。
iot_platform=# SET enable_skip_scan = on;ERROR: unrecognized configuration parameter "enable_skip_scan" iot_platform=#
? 步骤 3：验证跳跃扫描是否生效
-- 确认当前 PostgreSQL 版本（应为 18.x）SELECT version();
iot_platform=# SELECT version(); version ----------------------------------------------------------------------------------------- PostgreSQL 18.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 15.2.0) 15.2.0, 64-bit (1 row) iot_platform=#
-- 更新统计信息ANALYZE device_metrics;
iot_platform=# ANALYZE device_metrics;ANALYZEiot_platform=#
-- 执行查询并查看执行计划EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT * FROM device_metricsWHERE alert_level >= 3ORDER BY metric_time DESCLIMIT 100;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)iot_platform-# SELECT * FROM device_metricsiot_platform-# WHERE alert_level >= 3iot_platform-# ORDER BY metric_time DESCiot_platform-# LIMIT 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3975.59..3975.84 rows=100 width=30) (actual time=21.929..21.944 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Buffers: shared hit=834 -> Sort (cost=3975.59..4099.32 rows=49493 width=30) (actual time=21.925..21.934 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Sort Key: device_metrics.metric_time DESC Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=834 -> Seq Scan on public.device_metrics (cost=0.00..2084.00 rows=49493 width=30) (actual time=0.021..15.371 rows=49742.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Filter: (device_metrics.alert_level >= 3) Rows Removed by Filter: 50258 Buffers: shared hit=834 Planning: Buffers: shared hit=15 Planning Time: 0.501 ms Execution Time: 22.003 ms (17 rows) iot_platform=#
? 执行计划分析：查询走了全表扫描（Seq Scan），没有使用任何索引。这是因为 WHERE 条件中的列（alert_level）不是任何复合索引的第二列，跳跃扫描要求跳过第一列后，后续列需连续出现在索引中。当前查询条件不满足跳跃扫描的触发条件。
? 步骤 4：正确的跳跃扫描应用场景
? 场景1：只查询 metric_time，跳过 tenant_id
（使用
idx_tenant_time 进行跳跃扫描）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT * FROM device_metricsWHERE metric_time BETWEEN '2025-12-10' AND '2025-12-20'ORDER BY metric_timeLIMIT 100;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)iot_platform-# SELECT * FROM device_metricsiot_platform-# WHERE metric_time BETWEEN '2025-12-10' AND '2025-12-20'iot_platform-# ORDER BY metric_timeiot_platform-# LIMIT 100; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3617.02..3617.27 rows=100 width=30) (actual time=20.110..20.125 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Buffers: shared hit=834 -> Sort (cost=3617.02..3700.95 rows=33570 width=30) (actual time=20.107..20.116 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Sort Key: device_metrics.metric_time Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=834 -> Seq Scan on public.device_metrics (cost=0.00..2334.00 rows=33570 width=30) (actual time=0.031..15.813 rows=33313.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Filter: ((device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-20 00:00:00+00'::timestamp with time zone)) Rows Removed by Filter: 66687 Buffers: shared hit=834 Planning: Buffers: shared hit=6 Planning Time: 0.650 ms Execution Time: 20.174 ms (17 rows) iot_platform=#
? 场景2：查询 alert_level = 5 且时间范围，跳过 tenant_id
（使用
idx_tenant_alert_time 进行跳跃扫描）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT * FROM device_metricsWHERE alert_level = 5 AND metric_time BETWEEN '2025-12-10' AND '2025-12-20'ORDER BY metric_timeLIMIT 100;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)iot_platform-# SELECT * FROM device_metricsiot_platform-# WHERE alert_level = 5 iot_platform-# AND metric_time BETWEEN '2025-12-10' AND '2025-12-20'iot_platform-# ORDER BY metric_timeiot_platform-# LIMIT 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1151.15..1151.40 rows=100 width=30) (actual time=4.715..4.743 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Buffers: shared hit=870 -> Sort (cost=1151.15..1159.35 rows=3280 width=30) (actual time=4.712..4.724 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Sort Key: device_metrics.metric_time Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=870 -> Bitmap Heap Scan on public.device_metrics (cost=134.39..1025.79 rows=3280 width=30) (actual time=1.560..3.842 rows=3203.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Recheck Cond: ((device_metrics.alert_level = 5) AND (device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-20 00:00:00+00'::timestamp with time zone)) Heap Blocks: exact=812 Buffers: shared hit=870 -> Bitmap Index Scan on idx_tenant_alert_time (cost=0.00..133.57 rows=3280 width=0) (actual time=1.202..1.203 rows=3203.00 loops=1) Index Cond: ((device_metrics.alert_level = 5) AND (device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-20 00:00:00+00'::timestamp with time zone)) Index Searches: 11 Buffers: shared hit=58 Planning: Buffers: shared hit=5 Planning Time: 0.330 ms Execution Time: 4.897 ms (21 rows) iot_platform=#
? 关键点：对于 10 万行的小表，全表扫描成本较低，优化器选择顺序扫描是合理的。要真正体验跳跃扫描的威力，需要更大的数据量。
4️⃣ 大规模数据验证（500万+行）
? 步骤 1：插入 500 万行测试数据
INSERT INTO device_metricsSELECT (random()*99)::int, -- 100个租户 (random()*10000)::bigint, '2025-12-01 00:00:00'::timestamptz + (random()*30*24*3600) * interval '1 second', random()*40, (random()*5)::intFROM generate_series(1, 5000000);
iot_platform=# INSERT INTO device_metricsiot_platform-# SELECT iot_platform-# (random()*99)::int,iot_platform-# (random()*10000)::bigint,iot_platform-# '2025-12-01 00:00:00'::timestamptz + (random()*30*24*3600) * interval '1 second',iot_platform-# random()*40,iot_platform-# (random()*5)::intiot_platform-# FROM generate_series(1, 5000000);INSERT 0 5000000iot_platform=#
ANALYZE device_metrics;
iot_platform=# ANALYZE device_metrics;ANALYZEiot_platform=#
⏱️ 步骤 2：执行时间范围查询（跳跃扫描生效）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT * FROM device_metricsWHERE metric_time BETWEEN '2025-12-10' AND '2025-12-15'ORDER BY metric_timeLIMIT 1000;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)iot_platform-# SELECT * FROM device_metricsiot_platform-# WHERE metric_time BETWEEN '2025-12-10' AND '2025-12-15'iot_platform-# ORDER BY metric_timeiot_platform-# LIMIT 1000; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=92747.58..92864.05 rows=1000 width=30) (actual time=227.763..241.024 rows=1000.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Buffers: shared hit=2933 read=46630 written=3178 -> Gather Merge (cost=92747.58..191297.21 rows=846163 width=30) (actual time=227.761..240.944 rows=1000.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2933 read=46630 written=3178 -> Sort (cost=91747.56..92628.98 rows=352568 width=30) (actual time=178.870..178.949 rows=809.00 loops=3) Output: tenant_id, device_id, metric_time, temperature, alert_level Sort Key: device_metrics.metric_time Sort Method: top-N heapsort Memory: 167kB Buffers: shared hit=2933 read=46630 written=3178 Worker 0: actual time=154.899..154.990 rows=1000.00 loops=1 Sort Method: top-N heapsort Memory: 166kB Buffers: shared hit=28 read=10589 written=541 Worker 1: actual time=154.818..154.913 rows=1000.00 loops=1 Sort Method: top-N heapsort Memory: 167kB Buffers: shared hit=26 read=14257 written=1166 -> Parallel Bitmap Heap Scan on public.device_metrics (cost=24628.11..72416.63 rows=352568 width=30) (actual time=61.862..153.777 rows=283249.33 loops=3) Output: tenant_id, device_id, metric_time, temperature, alert_level Recheck Cond: ((device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-15 00:00:00+00'::timestamp with time zone)) Heap Blocks: exact=17656 Buffers: shared hit=2919 read=46628 written=3178 Worker 0: actual time=38.044..131.072 rows=211383.00 loops=1 Heap Blocks: exact=10589 Buffers: shared hit=20 read=10589 written=541 Worker 1: actual time=37.692..129.482 rows=285943.00 loops=1 Heap Blocks: exact=14255 Buffers: shared hit=20 read=14255 written=1166 -> Bitmap Index Scan on idx_tenant_alert_time (cost=0.00..24416.57 rows=846164 width=0) (actual time=102.396..102.396 rows=849748.00 loops=1) Index Cond: ((device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-15 00:00:00+00'::timestamp with time zone)) Index Searches: 701 Buffers: shared hit=2879 read=4128 Planning: Buffers: shared hit=35 Planning Time: 1.053 ms Execution Time: 241.225 ms (38 rows) iot_platform=#
? 跳跃扫描已生效！ 关键证据：
Index Searches: 701—— 这是 PostgreSQL 18 跳跃扫描的核心特征指标。优化器对每个(tenant_id, alert_level)组合执行一次索引探测，共 701 次，快速定位到metric_time范围内的数据。
5️⃣ 更深入的验证实验
? 实验 1：查看跳跃扫描的详细统计
SET track_io_timing = on;EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)SELECT COUNT(*) FROM device_metricsWHERE metric_time BETWEEN '2025-12-10' AND '2025-12-15';
iot_platform=# SET track_io_timing = on;SETiot_platform=#
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)iot_platform-# SELECT COUNT(*) FROM device_metricsiot_platform-# WHERE metric_time BETWEEN '2025-12-10' AND '2025-12-15'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=34993.62..34993.63 rows=1 width=8) (actual time=204.246..204.247 rows=1.00 loops=1) Output: count(*) Buffers: shared hit=303067 read=5719 I/O Timings: shared read=27.340 -> Index Only Scan using idx_tenant_alert_time on public.device_metrics (cost=0.43..32878.21 rows=846164 width=0) (actual time=0.307..161.232 rows=849748.00 loops=1) Output: tenant_id, alert_level, metric_time Index Cond: ((device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-15 00:00:00+00'::timestamp with time zone)) Heap Fetches: 0 Index Searches: 701 Buffers: shared hit=303067 read=5719 I/O Timings: shared read=27.340 Planning Time: 0.363 ms Execution Time: 204.327 ms (13 rows) iot_platform=#
? 实验 2：对比不同租户数量下的探测次数
SELECT COUNT(DISTINCT tenant_id) FROM device_metrics;SELECT COUNT(DISTINCT (tenant_id, alert_level)) FROM device_metrics;
iot_platform=# SELECT COUNT(DISTINCT tenant_id) FROM device_metrics; count ------- 100 (1 row) iot_platform=# SELECT COUNT(DISTINCT (tenant_id, alert_level)) FROM device_metrics; count ------- 600 (1 row) iot_platform=#
? 实验 3：验证跳跃扫描的智能性（更精细的时间范围）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT * FROM device_metricsWHERE metric_time BETWEEN '2025-12-12 12:00:00' AND '2025-12-12 13:00:00'ORDER BY metric_timeLIMIT 100;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)iot_platform-# SELECT * FROM device_metricsiot_platform-# WHERE metric_time BETWEEN '2025-12-12 12:00:00' AND '2025-12-12 13:00:00'iot_platform-# ORDER BY metric_timeiot_platform-# LIMIT 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=20298.84..20299.09 rows=100 width=30) (actual time=174.141..174.160 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Buffers: shared hit=119 read=6961 I/O Timings: shared read=80.445 -> Sort (cost=20298.84..20317.76 rows=7569 width=30) (actual time=174.139..174.151 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Sort Key: device_metrics.metric_time Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=119 read=6961 I/O Timings: shared read=80.445 -> Bitmap Heap Scan on public.device_metrics (cost=525.32..20009.56 rows=7569 width=30) (actual time=87.032..171.159 rows=7275.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Recheck Cond: ((device_metrics.metric_time >= '2025-12-12 12:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-12 13:00:00+00'::timestamp with time zone)) Heap Blocks: exact=6708 Buffers: shared hit=119 read=6961 I/O Timings: shared read=80.445 -> Bitmap Index Scan on idx_tenant_time (cost=0.00..523.43 rows=7569 width=0) (actual time=84.106..84.107 rows=7275.00 loops=1) Index Cond: ((device_metrics.metric_time >= '2025-12-12 12:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-12 13:00:00+00'::timestamp with time zone)) Index Searches: 102 Buffers: shared hit=103 read=269 I/O Timings: shared read=77.860 Planning Time: 0.208 ms Execution Time: 174.317 ms (23 rows) iot_platform=#
? 观察
Index Searches: 102—— 时间范围缩小后，探测次数相应减少，说明跳跃扫描动态适应数据分布。
? 实验 4：跳过不同列数的效果对比
? 场景 A：跳过1列（使用第2、3列）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT * FROM device_metricsWHERE alert_level = 3 AND metric_time BETWEEN '2025-12-10' AND '2025-12-15'ORDER BY metric_timeLIMIT 100;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)iot_platform-# SELECT * FROM device_metricsiot_platform-# WHERE alert_level = 3 iot_platform-# AND metric_time BETWEEN '2025-12-10' AND '2025-12-15'iot_platform-# ORDER BY metric_timeiot_platform-# LIMIT 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=53143.08..53154.73 rows=100 width=30) (actual time=121.026..136.814 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Buffers: shared hit=2153 read=40917 I/O Timings: shared read=30.180 -> Gather Merge (cost=53143.08..72662.52 rows=167597 width=30) (actual time=121.024..136.803 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2153 read=40917 I/O Timings: shared read=30.180 -> Sort (cost=52143.06..52317.64 rows=69832 width=30) (actual time=89.754..89.766 rows=76.00 loops=3) Output: tenant_id, device_id, metric_time, temperature, alert_level Sort Key: device_metrics.metric_time Sort Method: top-N heapsort Memory: 37kB Buffers: shared hit=2153 read=40917 I/O Timings: shared read=30.180 Worker 0: actual time=75.516..75.537 rows=100.00 loops=1 Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=554 read=13228 I/O Timings: shared read=8.107 Worker 1: actual time=73.607..73.617 rows=100.00 loops=1 Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=303 read=13995 I/O Timings: shared read=9.636 -> Parallel Bitmap Heap Scan on public.device_metrics (cost=5752.07..49474.13 rows=69832 width=30) (actual time=13.852..82.402 rows=56782.33 loops=3) Output: tenant_id, device_id, metric_time, temperature, alert_level Recheck Cond: ((device_metrics.alert_level = 3) AND (device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-15 00:00:00+00'::timestamp with time zone)) Heap Blocks: exact=13758 Buffers: shared hit=2139 read=40915 I/O Timings: shared read=30.146 Worker 0: actual time=0.789..68.177 rows=56244.00 loops=1 Heap Blocks: exact=13754 Buffers: shared hit=548 read=13226 I/O Timings: shared read=8.074 Worker 1: actual time=1.737..67.353 rows=58099.00 loops=1 Heap Blocks: exact=14270 Buffers: shared hit=295 read=13995 I/O Timings: shared read=9.636 -> Bitmap Index Scan on idx_tenant_alert_time (cost=0.00..5710.17 rows=167597 width=0) (actual time=31.596..31.596 rows=170347.00 loops=1) Index Cond: ((device_metrics.alert_level = 3) AND (device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-15 00:00:00+00'::timestamp with time zone)) Index Searches: 102 Buffers: shared hit=543 read=689 I/O Timings: shared read=4.644 Planning Time: 0.294 ms Execution Time: 137.003 ms (45 rows) iot_platform=#
? 场景 B：跳过2列（只用第3列）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT * FROM device_metricsWHERE metric_time BETWEEN '2025-12-10' AND '2025-12-15'ORDER BY metric_timeLIMIT 100;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)iot_platform-# SELECT * FROM device_metricsiot_platform-# WHERE metric_time BETWEEN '2025-12-10' AND '2025-12-15'iot_platform-# ORDER BY metric_timeiot_platform-# LIMIT 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=86891.55..86903.20 rows=100 width=30) (actual time=251.514..260.836 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Buffers: shared hit=1338 read=48222 I/O Timings: shared read=50.815 -> Gather Merge (cost=86891.55..185441.18 rows=846163 width=30) (actual time=251.511..260.824 rows=100.00 loops=1) Output: tenant_id, device_id, metric_time, temperature, alert_level Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1338 read=48222 I/O Timings: shared read=50.815 -> Sort (cost=85891.53..86772.95 rows=352568 width=30) (actual time=227.550..227.559 rows=73.67 loops=3) Output: tenant_id, device_id, metric_time, temperature, alert_level Sort Key: device_metrics.metric_time Sort Method: top-N heapsort Memory: 37kB Buffers: shared hit=1338 read=48222 I/O Timings: shared read=50.815 Worker 0: actual time=216.430..216.442 rows=100.00 loops=1 Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=26 read=12441 I/O Timings: shared read=1.123 Worker 1: actual time=216.053..216.063 rows=100.00 loops=1 Sort Method: top-N heapsort Memory: 36kB Buffers: shared hit=25 read=15928 I/O Timings: shared read=1.512 -> Parallel Bitmap Heap Scan on public.device_metrics (cost=24628.11..72416.63 rows=352568 width=30) (actual time=116.709..204.603 rows=283249.33 loops=3) Output: tenant_id, device_id, metric_time, temperature, alert_level Recheck Cond: ((device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-15 00:00:00+00'::timestamp with time zone)) Heap Blocks: exact=14136 Buffers: shared hit=1324 read=48220 I/O Timings: shared read=50.798 Worker 0: actual time=106.008..194.665 rows=249002.00 loops=1 Heap Blocks: exact=12439 Buffers: shared hit=20 read=12439 I/O Timings: shared read=1.106 Worker 1: actual time=105.586..192.121 rows=318096.00 loops=1 Heap Blocks: exact=15925 Buffers: shared hit=17 read=15928 I/O Timings: shared read=1.512 -> Bitmap Index Scan on idx_tenant_alert_time (cost=0.00..24416.57 rows=846164 width=0) (actual time=131.268..131.268 rows=849748.00 loops=1) Index Cond: ((device_metrics.metric_time >= '2025-12-10 00:00:00+00'::timestamp with time zone) AND (device_metrics.metric_time <= '2025-12-15 00:00:00+00'::timestamp with time zone)) Index Searches: 701 Buffers: shared hit=1287 read=5717 I/O Timings: shared read=46.476 Planning Time: 0.222 ms Execution Time: 261.011 ms (45 rows) iot_platform=#
6️⃣ 物联网实时监控场景完整验证
⏰ 步骤 1：插入当前时间范围的数据（模拟实时数据）
-- 插入最近1小时的数据（模拟实时物联网数据）INSERT INTO device_metrics (tenant_id, device_id, metric_time, temperature, alert_level)SELECT (random()*99)::int, (random()*10000)::bigint, NOW() - (random()*3600) * interval '1 second', 25 + random()*20, (CASE WHEN random()*40 > 35 THEN 5 ELSE (random()*3)::int END)FROM generate_series(1, 50000);
iot_platform=# INSERT INTO device_metrics (tenant_id, device_id, metric_time, temperature, alert_level)iot_platform-# SELECT iot_platform-# (random()*99)::int,iot_platform-# (random()*10000)::bigint,iot_platform-# NOW() - (random()*3600) * interval '1 second',iot_platform-# 25 + random()*20,iot_platform-# (CASE WHEN random()*40 > 35 THEN 5 ELSE (random()*3)::int END)iot_platform-# FROM generate_series(1, 50000);INSERT 0 50000iot_platform=#
-- 插入最近1天的数据（用于统计分析）INSERT INTO device_metrics (tenant_id, device_id, metric_time, temperature, alert_level)SELECT (random()*99)::int, (random()*10000)::bigint, NOW() - (random()*86400) * interval '1 second', 15 + random()*30, (random()*5)::intFROM generate_series(1, 200000);
iot_platform=# INSERT INTO device_metrics (tenant_id, device_id, metric_time, temperature, alert_level)iot_platform-# SELECT iot_platform-# (random()*99)::int,iot_platform-# (random()*10000)::bigint,iot_platform-# NOW() - (random()*86400) * interval '1 second',iot_platform-# 15 + random()*30,iot_platform-# (random()*5)::intiot_platform-# FROM generate_series(1, 200000);INSERT 0 200000iot_platform=#
? 步骤 2：查看数据时间范围
SELECT MIN(metric_time) as 最早时间, MAX(metric_time) as 最晚时间, COUNT(*) as 总记录数FROM device_metrics;
iot_platform=# SELECT iot_platform-# MIN(metric_time) as 最早时间,iot_platform-# MAX(metric_time) as 最晚时间,iot_platform-# COUNT(*) as 总记录数iot_platform-# FROM device_metrics; 最早时间 | 最晚时间 | 总记录数 -------------------------------+-------------------------------+---------- 2025-12-01 00:00:01.052681+00 | 2026-04-23 09:28:33.252303+00 | 5350000 (1 row) iot_platform=#
? 步骤 3：物联网典型查询验证
? 查询 1：最近1小时的高温异常
SELECT COUNT(*) as 实时数据量, MIN(metric_time) as 最早时间, MAX(metric_time) as 最新时间, AVG(temperature) as 平均温度, COUNT(CASE WHEN temperature > 35 THEN 1 END) as 高温异常数FROM device_metricsWHERE metric_time >= NOW() - INTERVAL '1 hour';
iot_platform=# SELECT iot_platform-# COUNT(*) as 实时数据量,iot_platform-# MIN(metric_time) as 最早时间,iot_platform-# MAX(metric_time) as 最新时间,iot_platform-# AVG(temperature) as 平均温度,iot_platform-# COUNT(CASE WHEN temperature > 35 THEN 1 END) as 高温异常数iot_platform-# FROM device_metricsiot_platform-# WHERE metric_time >= NOW() - INTERVAL '1 hour'; 实时数据量 | 最早时间 | 最新时间 | 平均温度 | 高温异常数 ------------+-------------------------------+-------------------------------+--------------------+------------ 53668 | 2026-04-23 08:32:46.336945+00 | 2026-04-23 09:28:33.252303+00 | 34.305935567425834 | 25679 (1 row) iot_platform=#
? 查询 2：跨租户告警统计（最近1天）
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)SELECT alert_level, COUNT(*) as 告警次数, AVG(temperature) as 平均温度, COUNT(DISTINCT device_id) as 受影响设备数FROM device_metricsWHERE metric_time >= NOW() - INTERVAL '1 day' AND alert_level >= 3GROUP BY alert_levelORDER BY alert_level;
iot_platform=# EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)iot_platform-# SELECT iot_platform-# alert_level,iot_platform-# COUNT(*) as 告警次数,iot_platform-# AVG(temperature) as 平均温度,iot_platform-# COUNT(DISTINCT device_id) as 受影响设备数iot_platform-# FROM device_metricsiot_platform-# WHERE metric_time >= NOW() - INTERVAL '1 day'iot_platform-# AND alert_level >= 3iot_platform-# GROUP BY alert_leveliot_platform-# ORDER BY alert_level; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=2360.54..2363.94 rows=6 width=26) (actual time=113.171..129.011 rows=3.00 loops=1) Output: alert_level, count(*), avg(temperature), count(DISTINCT device_id) Group Key: device_metrics.alert_level Buffers: shared hit=2555 read=1132, temp read=472 written=473 I/O Timings: shared read=8.848, temp read=1.070 write=2.344 -> Sort (cost=2360.54..2361.21 rows=266 width=18) (actual time=102.077..114.362 rows=113268.00 loops=1) Output: alert_level, temperature, device_id Sort Key: device_metrics.alert_level, device_metrics.device_id Sort Method: external merge Disk: 3776kB Buffers: shared hit=2555 read=1132, temp read=472 written=473 I/O Timings: shared read=8.848, temp read=1.070 write=2.344 -> Bitmap Heap Scan on public.device_metrics (cost=1342.15..2349.83 rows=266 width=18) (actual time=23.022..40.725 rows=113268.00 loops=1) Output: alert_level, temperature, device_id Recheck Cond: ((device_metrics.alert_level >= 3) AND (device_metrics.metric_time >= (now() - '1 day'::interval))) Heap Blocks: exact=2084 Buffers: shared hit=2555 read=1132 I/O Timings: shared read=8.848 -> Bitmap Index Scan on idx_tenant_alert_time (cost=0.00..1342.08 rows=266 width=0) (actual time=22.746..22.747 rows=113268.00 loops=1) Index Cond: ((device_metrics.alert_level >= 3) AND (device_metrics.metric_time >= (now() - '1 day'::interval))) Index Searches: 301 Buffers: shared hit=479 read=1124 I/O Timings: shared read=8.122 Planning: Buffers: shared hit=1 read=5 I/O Timings: shared read=0.054 Planning Time: 0.435 ms Execution Time: 129.777 ms (27 rows) iot_platform=#
? 查询 3：设备异常趋势（每分钟统计）
SELECT
DATE_TRUNC('minute', metric_time) as 分钟, COUNT(*) as 数据点, AVG(temperature) as 平均温度, MAX(temperature) as 峰值温度FROM device_metricsWHERE metric_time >= NOW() - INTERVAL '10 minutes'
AND temperature > 30GROUP BY DATE_TRUNC('minute', metric_time)ORDER BY 分钟 DESC;
iot_platform=# SELECT iot_platform-# DATE_TRUNC('minute', metric_time) as 分钟,iot_platform-# COUNT(*) as 数据点,iot_platform-# AVG(temperature) as 平均温度,iot_platform-# MAX(temperature) as 峰值温度iot_platform-# FROM device_metricsiot_platform-# WHERE metric_time >= NOW() - INTERVAL '10 minutes'iot_platform-# AND temperature > 30iot_platform-# GROUP BY DATE_TRUNC('minute', metric_time)iot_platform-# ORDER BY 分钟 DESC;
分钟 | 数据点 | 平均温度 | 峰值温度
------------------------+--------+--------------------+--------------------
2026-04-23 09:28:00+00 | 33 | 37.18290660208073 | 44.06416868280394
2026-04-23 09:27:00+00 | 693 | 37.761635971732446 | 44.99652298541822
2026-04-23 09:26:00+00 | 677 | 37.33675349278627 | 44.97645846172219
2026-04-23 09:25:00+00 | 684 | 37.712624529431665 | 44.9988998702563
2026-04-23 09:24:00+00 | 325 | 37.4298072584499 | 44.952187032317816
(5 rows)
iot_platform=#
? 总结
| 特性 | 说明 |
|---|---|
| ? 自动优化 | PostgreSQL 18 跳跃扫描默认启用，无需手动配置 |
| ? 适用场景 | 复合索引的非前缀查询，尤其是前置列基数较低时效果最佳 |
| ⚡ 性能提升 | 将全表扫描转换为多次小范围索引探测，响应时间从秒级降至毫秒级 |
| ? 物联网价值 | 解决租户隔离、设备多维查询等典型场景下的索引失效问题 |
| ? 运维友好 | 无需改造表结构或业务 SQL，升级即可获得优化 |
? 最终建议：对于物联网平台， 优先选择 PostgreSQL 兼容路线，结合跳跃扫描等新特性，既能享受时序场景的高性能，又能复用成熟的 SQL 生态和运维工具链，是面向未来的稳妥之选。
? 作者注
—— 本文所有操作及测试均基于 openEuler 22.03 (LTS-SP4) 操作系统、 PostgreSQL 18.3 官方镜像 版本完成，核心围绕 Docker 容器化一键部署实战 PostgreSQL 18 跳跃扫描展开。请注意，PostgreSQL 社区版本处于持续迭代更新中，细节可能存在个人实践理解偏差，请以 PostgreSQL 全球官方文档 最新内容为准。
—— 本文仅为个人学习实践总结，不代表任何组织、社区及厂商官方观点。
? 欢迎关注公众号「shunwah星辰数智社」，一起交流数据库运维与物联网技术实战。