Postgresql在中等大小的表上进行简单查询的速度非常慢

Question

我在Google SQL上有一个PostgresSQL数据库：

• 4 vCPUS
• 15 GB内存
• 55 GB SSD

相关模式为：

postgres=> d device;
Table "public.device"
Column          |          Type           | Collation | Nullable | Default
-------------------------+-------------------------+-----------+----------+---------
id                      | uuid                    |           | not null |
brand                   | character varying(255)  |           |          |
model                   | character varying(255)  |           |          |
serialnumber            | character varying(255)  |           |          |
[...]
Indexes:
"device_pkey" PRIMARY KEY, btree (id)
[...]
Referenced by:
TABLE "application" CONSTRAINT "fk_application_device_id_device" FOREIGN KEY (device_id) REFERENCES device(id) ON DELETE CASCADE
[...]
postgres=> d application;
Table "public.application"
Column           |          Type          | Collation | Nullable | Default
----------------------------+------------------------+-----------+----------+---------
id                         | uuid                   |           | not null |
device_id                  | uuid                   |           | not null |
packagename                | character varying(255) |           |          |
versionname                | character varying(255) |           |          |
[...]
Indexes:
"application_pkey" PRIMARY KEY, btree (id)
"application_device_id_packagename_key" UNIQUE CONSTRAINT, btree (device_id, packagename)
Foreign-key constraints:
"fk_application_device_id_device" FOREIGN KEY (device_id) REFERENCES device(id) ON DELETE CASCADE
[...]

音量：

• device表：16k行
• application:360万行

当尝试像这样简单的东西时

select count(id) from application;

该查询花了900秒(原文如此(来计算这360万行。

以下是执行计划：

postgres=> explain analyze select count(id) from application;
      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate  (cost=1245180.18..1245180.19 rows=1 width=8) (actual time=311470.250..311496.933 rows=1 loops=1)
->  Gather  (cost=1245179.96..1245180.17 rows=2 width=8) (actual time=311470.225..311496.919 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Partial Aggregate  (cost=1244179.96..1244179.97 rows=1 width=8) (actual time=311463.287..311463.289 rows=1 loops=3)
->  Parallel Seq Scan on application  (cost=0.00..1234885.77 rows=3717677 width=16) (actual time=79.783..311296.505 rows=1202169 loops=3)
Planning Time: 0.083 ms
Execution Time: 311497.021 ms
(8 rows)

似乎所有内容(如键和索引(都设置正确，那么这个简单的查询花费这么长时间的原因是什么呢？

Answer 1

您必须深入查看以确定原因：

• 在PostgreSQL配置中打开track_io_timing，这样您就可以看到I/O需要多长时间

• 使用EXPLAIN (ANALYZE, BUFFERS)查看触摸了多少8kB块

如果块的数量非常多，那么表就会膨胀(几乎什么都不包含(，并且顺序扫描需要很长时间，因为它必须读取所有的空白空间。VACUUM (FULL)可以帮助做到这一点。

如果块数如您所料，那么问题是您的存储太慢。