QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=21267.11..21286.98 rows=461 width=31) (actual time=1.711..1.712 rows=1 loops=1)
Group Key: *
\-\> Sort (cost=21267.11..21268.91 rows=719 width=35) (actual time=1.564..1.591 rows=719 loops=1)
Sort Key: *
Sort Method: quicksort Memory: 69kB
\-\> Nested Loop (cost=70.03..21233.00 rows=719 width=35) (actual time=0.483..1.454 rows=719 loops=1)
\-\> Index Scan using * (cost=0.28..8.30 rows=1 width=27) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: *
\-\> Bitmap Heap Scan on measurements m (cost=69.75..21213.91 rows=719 width=32) (actual time=0.240..0.994 rows=719 loops=1)
Recheck Cond: *
Filter: *
Rows Removed by Filter: 5241
Heap Blocks: exact=50
\-\> Bitmap Index Scan on * (cost=0.00..69.57 rows=6018 width=0) (actual time=0.224..0.224 rows=5960 loops=1)
Index Cond: *
Planning Time: 0.697 ms
**Execution Time: 1.766 ms**
(17 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=21267.11..21286.98 rows=461 width=31) (actual time=0.897..0.898 rows=1 loops=1)
Group Key: *
\-\> Sort (cost=21267.11..21268.91 rows=719 width=35) (actual time=0.795..0.831 rows=719 loops=1)
Sort Key: *
Sort Method: quicksort Memory: 69kB
\-\> Nested Loop (cost=70.03..21233.00 rows=719 width=35) (actual time=0.178..0.718 rows=719 loops=1)
\-\> Index Scan using * (cost=0.28..8.30 rows=1 width=27) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: *
\-\> Bitmap Heap Scan on measurements m (cost=69.75..21213.91 rows=719 width=32) (actual time=0.081..0.457 rows=719 loops=1)
Recheck Cond: *
Filter: *
Rows Removed by Filter: 5241
Heap Blocks: exact=50
\-\> Bitmap Index Scan on * (cost=0.00..69.57 rows=6018 width=0) (actual time=0.073..0.073 rows=5960 loops=1)
Index Cond: *
Planning Time: 0.336 ms
**Execution Time: 0.929 ms**
(17 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=21267.11..21286.98 rows=461 width=31) (actual time=0.873..0.873 rows=1 loops=1)
Group Key: *
\-\> Sort (cost=21267.11..21268.91 rows=719 width=35) (actual time=0.794..0.813 rows=719 loops=1)
Sort Key: *
Sort Method: quicksort Memory: 69kB
\-\> Nested Loop (cost=70.03..21233.00 rows=719 width=35) (actual time=0.168..0.717 rows=719 loops=1)
\-\> Index Scan using * (cost=0.28..8.30 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: *
\-\> Bitmap Heap Scan on measurements m (cost=69.75..21213.91 rows=719 width=32) (actual time=0.071..0.457 rows=719 loops=1)
Recheck Cond: *
Filter: *
Rows Removed by Filter: 5241
Heap Blocks: exact=50
\-\> Bitmap Index Scan on *
(cost=0.00..69.57 rows=6018 width=0) (actual time=0.063..0.063 rows=5960 loops=1)
Index Cond: *
Planning Time: 0.304 ms
**Execution Time: 0.903 ms**
(17 rows)
\---------------------------------------------------------------------------------------------------------------
Time (mean ± σ): **98.1 ms** ± 28.1 ms \[User: 30.7 ms, System: 11.1 ms\]Range (min … max): 75.6 ms … 129.5 ms 3 runs
\---------------------------------------------------------------------------------------------------------------
我正在使用 hyperfine 对 PostgreSQL 中的查询性能进行基准测试。我使用--runs 3选项运行了三遍。正如您所看到的，我运行查询的所有 3 次的执行时间分别花费了 1.766、0.929 和 0.903 毫秒。我的问题是，为什么平均值 = 98.1 毫秒？这句话代表什么意思呢？因为执行时间在 0.9 ms - 1.7 ms 之间是没有任何意义的，而它们的平均值是 98.1 ms。我测试在 Postico 中执行相同的查询，花费了 0.903 毫秒。我只是好奇如果平均值不代表执行平均时间，它代表什么。
我的印象是 Hyperfine 测量完整的端到端调用，包括访问容器、启动 psql 和执行查询。如果您只想测量查询，
pgbench可能是一个更好的工具。它可以将文件作为输入，并包含您的特定查询。请参阅https://www.postgresql.org/docs/current/pgbench.html