发现缺失的 PostgreSQL 索引
如果您正在寻找一个完美的方法来以最有效的方式破坏性能，那么缺少索引是一个关键因素。🙂 但是，如果您想确保您的数据库运行良好，并且如果您通常不赞成用户投诉 - 最好注意丢失的索引并确保正确处理所有相关表。一个 PostgreSQL 索引可以在性能上产生巨大的差异。
为了帮助您理解，我编写了这个小指南，介绍如何定位丢失的索引、如何修复它们以及如何获得良好的数据库性能。
设置测试数据库
为了演示如何查找丢失的索引，我必须首先创建一个测试数据库。一种方法是使用 pgbench：
[hs@hansmacbook ~]$ pgbench -i -s 100 test
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
10000000 of 10000000 tuples (100%) done (elapsed 13.65 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 19.92 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 13.70 s, vacuum 1.95 s, primary keys 4.27 s).
这里发生的是 pgbench 刚刚为我们提供了一个包含 4 个表的小示例数据库。最大的一个是 pgbench_accounts：
test=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------------+-------+-------+-------------+---------------+---------+-------------
public | pgbench_accounts | table | hs | permanent | heap | 1281 MB |
public | pgbench_branches | table | hs | permanent | heap | 40 kB |
public | pgbench_history | table | hs | permanent | heap | 0 bytes |
public | pgbench_tellers | table | hs | permanent | heap | 80 kB |
(4 rows)
这个数据库默认是完美索引的，所以我们必须删除一些索引才能找到我们以后可以修复的东西：
test=# \d pgbench_accounts
Table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
test=# ALTER TABLE pgbench_accounts
DROP CONSTRAINT pgbench_accounts_pkey;
ALTER TABLE
我们只是删除了主键：在内部，它只是一个不允许 NULL 条目的唯一索引。
运行演示基准
在我们开始运行基准测试以查看性能到底有多差之前，您需要确保已安装并激活了处理性能问题的最重要工具：pg_stat_statements。如果没有 pg_stat_statements，跟踪性能问题会变得非常困难。
因此考虑执行以下步骤来安装 pg_stat_statements：
- 将“pg_stat_statements”添加到 shared_preload_libraries (postgresql.conf)
- 重启数据库
- 在您的数据库中运行“CREATE EXTENSION pg_stat_statements”
一旦完成，我们就可以运行我们的基准测试了。让我们看看发生了什么：
[hs@hansmacbook ~]$ pgbench -c 10 -T 60 -j 10 test
pgbench (14.1)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 10
number of threads: 10
duration: 60 s
number of transactions actually processed: 252
latency average = 2446.148 ms
initial connection time = 8.833 ms
<b>tps = 4.088061</b> (without initial connection time)
尽管打开了 10 个连接（-c 10）并证明 pgbench 有 10 个线程（-j 10），但我们还是设法每秒运行 4 次（是的，4 次）事务。有人可能会争辩说硬件是问题所在，但绝对不是：
Model Name: MacBook Pro
Model Identifier: MacBookPro16,1
Processor Name: 8-Core Intel Core i9
Processor Speed: 2,3 GHz
Number of Processors: 1
Total Number of Cores: 8
这是一台现代的 8 核机器。即使时钟速度是原来的 10 倍，我们也会以每秒 40 笔交易的速度达到顶峰。这仍然远低于您的预期。
pg_stat_user_tables：PostgreSQL 索引的重要监控视图
可以在 pg_stat_user_tables 中找到索引可能丢失的第一条线索。下表包含相关列：
test=# \d pg_stat_user_tables
View "pg_catalog.pg_stat_user_tables"
Column | Type ...
---------------------+---------------- …
relid | oid ...
schemaname | name ...
relname | name ...
seq_scan | bigint ...
seq_tup_read | bigint ...
idx_scan | bigint ...
...
我们在这里看到的是表名（relname），包括模式名。然后我们可以看到我们的表被顺序读取的频率（seq_scan）以及索引被使用的频率（idx_scan）。最后，还有最相关的信息：seq_tup_read。那是什么意思？它实际上告诉我们系统必须处理多少行才能满足所有这些顺序扫描。这个数字真的很重要，我怎么强调都不为过：如果“非常经常”读取“很多”，它将导致 seq_tup_read 列中出现疯狂的条目。这也意味着我们必须处理大量的行来一次又一次地顺序读取一个表。
现在，让我们运行一个非常重要的查询：
test=# SELECT schemaname,
relname,
seq_scan,
seq_tup_read,
idx_scan,
seq_tup_read / seq_scan AS avg
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;
schemaname | relname | seq_scan | seq_tup_read | idx_scan | avg
------------+------------------+----------+--------------+----------+---------
public | pgbench_accounts | 954 | <b>5050000000</b> | | 5293501
public | pgbench_branches | 254 | 25400 | 0 | 100
public | pgbench_tellers | 1 | 1000 | 252 | 1000
(3 rows)
这是真正的魔法。它返回那些被顺序扫描命中最多的表，并告诉我们顺序扫描平均命中了多少行。在我们的顶级查询中，顺序扫描平均读取了 500 万行，并且根本没有使用索引。这为我们提供了一个明确的指标，表明该表有问题。如果您碰巧知道该应用程序，一个简单的 \d 将发现最明显的问题。但是，让我们更深入地挖掘并证实我们的怀疑：
pg_stat_statements：查找慢查询
如前所述，pg_stat_statements 确实是查找慢查询的黄金标准。通常，出现在 pg_stat_user_tables 中的那些表也会在 pg_stat_statements 中显示的一些最差查询中排名靠前。
下面的查询可以揭开真相：
test=# SELECT query,
total_exec_time,
calls,
mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC;
-[ RECORD 1 ]---+-------------------------------------------------------------------
query | UPDATE pgbench_accounts
SET abalance = abalance + $1 WHERE aid = $2
total_exec_time | 433708.0210760001
calls | 252
mean_exec_time | 1721.0635756984136
-[ RECORD 2 ]---+-------------------------------------------------------------------
query | SELECT abalance FROM pgbench_accounts
WHERE aid = $1
total_exec_time | 174819.2974120001
calls | 252
mean_exec_time | 693.7273706825395
…
哇，顶级查询的平均执行时间为 1.721 秒！那是很多。如果我们检查查询，我们会发现只有一个简单的 WHERE 子句过滤“aid”。如果我们看一下表格，我们会发现没有关于“援助”的索引——从性能的角度来看，这是致命的。
进一步检查第二个查询将发现完全相同的问题。
改进您的 PostgreSQL 索引和基准测试
让我们部署索引并重置 pg_stat_statements 以及 PostgreSQL 创建的正常系统统计信息：
test=# CREATE UNIQUE INDEX idx_accounts
ON pgbench_accounts (aid);
CREATE INDEX
test=# SELECT pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 row)
test=# SELECT pg_stat_reset();
pg_stat_reset
---------------
(1 row)
部署缺失的索引后，我们可以再次运行测试，看看会发生什么：
[hs@hansmacbook ~]$ pgbench -c 10 -T 60 -j 10 test
pgbench (14.1)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 10
number of threads: 10
duration: 60 s
number of transactions actually processed: 713740
latency average = 0.841 ms
initial connection time = 7.541 ms
<b>tps = 11896.608085</b> (without initial connection time)
多么好的进步。数据库速度提高了 3000 倍。世界上没有“更好的硬件”可以为我们提供这种类型的改进。这里的要点是，在相关位置缺少一个 PostgreSQL 索引可能会破坏整个数据库并使整个系统保持忙碌而不会产生有用的性能。
真正重要的是要记住我们解决问题的方式。pg_stat_user_tables 是一个很好的指标，可以帮助你找出问题所在。然后您可以检查 pg_stat_statements 并查找最差的查询。按 total_exec_time DESC 排序是关键。
最后
如果您想了解更多关于 PostgreSQL 和一般数据库性能的信息，我强烈推荐 Laurenz Albe 关于“ Count(*) made fast ”的帖子或他最近的题为Query Parameter Data Types and Performance的帖子。
作者：Hans-Jürgen Schönig
Hans-Jürgen Schönig 自 90 年代以来就有使用 PostgreSQL 的经验。他是 CYBERTEC 的首席执行官和技术负责人，该公司是该领域的市场领导者之一，自 2000 年以来已为全球无数客户提供服务。
文章来源：https://www.cybertec-postgresql.com/en/find-and-fix-a-missing-postgresql-index/
