作者： 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 等），带来的收益非常明确：

对于物联网行业而言， “稳妥”不等于保守，而是在设备数量从 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 兼容路线，结合跳跃扫描等新特性，既能享受时序场景的高性能，又能复用成熟的 SQL 生态和运维工具链，是面向未来的稳妥之选。

📝 作者注

—— 本文所有操作及测试均基于  openEuler 22.03 (LTS-SP4) 操作系统、 PostgreSQL 18.3 官方镜像 版本完成，核心围绕 Docker 容器化一键部署实战  PostgreSQL 18 跳跃扫描展开。请注意，PostgreSQL 社区版本处于持续迭代更新中，细节可能存在个人实践理解偏差，请以  PostgreSQL 全球官方文档 最新内容为准。

—— 本文仅为个人学习实践总结，不代表任何组织、社区及厂商官方观点。