PostgreSQL 查询计划器充满了惊喜，因此编写高性能查询的常识性方法有时会产生误导。在这篇博文中，我将描述借助 EXPLAIN ANALYZE 和 Postgres 元数据分析优化看似显而易见的查询的示例。
所有测试查询都是在 PostgreSQL 12 上针对一百万个对象的表执行的。如果您想使用较小的开发数据集复制类似的行为，则必须通过运行以下命令来阻止使用顺序扫描：
代码语言：javascript复制SET enable_seqscan TO off;本教程假定您对阅读 EXPLAIN ANALYZE 报告有一定的基本了解。您可以查看此博客文章以了解该主题的介绍。
1. 通过函数调用搜索
通过使用 PostgreSQL 函数调用修改的值进行搜索是很常见的。让我们看一下通过小写值搜索列的查询计划：
代码语言：javascript复制EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE lower(email) = 'email@example.com' ;
-> Parallel Seq Scan on users
Filter: (lower((email)::text) = 'email@example.com'::text)
Rows Removed by Filter: 333667
Buffers: shared hit=1248 read=41725
Execution Time: 180.813 ms该报告表明查询计划程序执行低效的Seq ScanFilterBUFFERS来执行查询。由于在查询中添加了选项，我们可以看到数据库必须使用慢速磁盘读取操作来获取超过 40k 的数据页，并且其中只有大约 1k被缓存在内存中。
按函数搜索的查询不能使用标准索引。因此，您需要添加自定义索引以使其高效。但是，在每个查询的基础上添加自定义索引并不是一种非常可扩展的方法。您可能会发现自己有多个冗余索引，这些索引会减慢写入操作。
如果大小写字母无关紧要，您可以运行迁移以将所有值小写，并使标准索引正常工作。但是，如果您仍想在数据库中存储大写字符，您可以考虑使用CITEXT 扩展名。它创建了一个不区分大小写的列，可以在不创建自定义索引的情况下进行高效搜索。
代码语言：javascript复制EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'Email@exaMple.Com' ;
Index Scan using index_users_on_email on users
Index Cond: (email = 'Email@exaMple.Com'::citext)
Buffers: shared hit=3
Execution Time: 0.128 ms原始查询的180 毫秒执行时间可能看起来并不多。但我们刚刚设法将其加速了几个数量级，降至 1毫秒以下！无论数据大小如何，新解决方案都将保持高性能，并且查询仅从内存缓存中获取三个缓冲区块。此外，通过利用扩展，我们可以避免添加额外的索引。
2. 按模式搜索
LIKE和ILIKE查询经常被使用，但并不总是很明显，需要额外的设置来有效地执行它们。让我们看看示例查询在标准 B 树索引下的表现：
EXPLAIN ANALYZE SELECT * FROM users
WHERE email LIKE '%@example.com';
-> Parallel Seq Scan on users
Filter: ((email)::text ~~ '%@example.com'::text)
Execution Time: 111.263 ms
和以前一样，查询计划器无法利用索引，不得不求助于低效Seq ScanFilter.
为了加快这个查询的速度，我们必须添加一个自定义扩展和索引类型。运行以下命令：
代码语言：javascript复制CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX index_users_on_email_gin ON users USING gin (email gin_trgm_ops);让我们重新运行我们的查询：
EXPLAIN ANALYZE SELECT * FROM users
WHERE email LIKE '%@example.com';
Bitmap Heap Scan on users
Recheck Cond: ((email)::text ~~ '%@example.com'::text)
-> Bitmap Index Scan on index_users_on_email_gin
Index Cond: ((email)::text ~~ '%@example.com'::text)
Execution Time: 0.206 ms现在它在1ms以下执行。请记住，gin索引的更新速度比标准的要慢。因此，您应该避免将它们添加到经常更新的表中。
3.按NULLS LAST排序
按 NULLS LAST 排序，除非将列配置为 NOT NULL，否则在使用它进行排序时必须小心。默认的ASC 顺序将始终在结果末尾返回 NULL 值。但是，如果您想按降序对可能为 NULL 的字符串进行排序，但将所有 NULL 保留在最后怎么办？
一种初始方法可能是利用 NULLS LAST 自定义排序顺序。
让我们仔细看看这些查询会生成的 EXPLAIN ANALYZE 输出：
代码语言：javascript复制EXPLAIN ANALYZE
SELECT * FROM users
ORDER BY email DESC LIMIT 10;
-> Index Scan Backward using index_users_on_email on users
Execution Time: 0.641 ms我们可以看到一个Index Scan Backward条目，因此我们的查询正确地使用了索引，并且几乎立即执行。但是，此查询的结果将始终从NULL值开始。因此，如果我们想将它们移动到相应的末尾，我们可以像这样重写它：
代码语言：javascript复制EXPLAIN ANALYZE
SELECT * FROM users
ORDER BY email DESC NULLS LAST LIMIT 10;
-> Sort (cost=41482.85..42525.55 rows=417083 width=41) (actual time=5572.524..5572.589 rows=8 loops=3)
Sort Key: email DESC NULLS LAST
Sort Method: top-N heapsort Memory: 26kB
-> Parallel Seq Scan on users (cost=0.00..32469.83 rows=417083 width=41) (actual time=0.037..2793.011 rows=333667 loops=3)
Execution Time: 5578.725 ms但正如您所看到的查询，现在执行了超过5 SECONDS。尽管email列被索引，但标准索引不能用于带有NULLS LAST选项的排序。相反，数据库必须在内存中对整个表进行排序，或者退回到更慢的磁盘排序。它不仅会降低性能，而且还会显的增加整体内存使用量。
您可以通过添加自定义索引来修复它，NULLS LAST如PostgreSQL 文档中所述。但是，就像在按函数搜索的情况下一样，在每个查询的基础上添加自定义索引是一种不好的做法。
获得所需结果的一种简单方法是编写两个查询。第一个将获取已排序的非空值。如果结果不满足LIMIT，则另一个查询会获取剩余的带有NULL值的行。
代码语言：javascript复制SELECT *
FROM users ORDER BY email DESC
WHERE email IS NOT NULL LIMIT 10;
SELECT *
FROM users
WHERE email IS NULL LIMIT 10;4.Bloated null_indexes
正如我们在前面的示例中确定的那样，添加正确的索引可以显着提高查询执行时间。但是，过度使用索引会大大增加数据库的大小并增加维护内存的使用。此外，必须在每次写入操作时更新索引。所以限制它们的数量和范围通常是一个好方法。
您的数据库可能有一些所谓的（我认为）“NULL 索引”。这些是包含高比率NULL值的索引。
根据业务逻辑，NULL可能会使用一个值进行搜索，因此这些索引是正确的。但是通常您不会编写查询来搜索包含特定NULL值的行。如果是这种情况，重新创建索引以排除NULLs 将减少磁盘使用量并限制必须更新的频率。
您可以运行以下命令来删除和重建索引以仅包含NOT NULL行：
代码语言：javascript复制DROP INDEX CONCURRENTLY users_reset_token_ix;
CREATE INDEX CONCURRENTLY users_reset_token_ix ON users(reset_token)
WHERE reset_token IS NOT NULL;值得注意的是，这个索引仍然可以被显式搜索所有NOT NULL值的查询使用。
您可以查看PG Extrasnull_indexes方法（或执行其原始 SQL 源代码）以查看您的数据库是否有许多可以削减的索引以及预期的磁盘空间节省：
代码语言：javascript复制index | index_size | unique | indexed_column | null_frac | expected_saving
-------------------- ------------ -------- ---------------- ----------- -----------------
users_reset_token | 1445 MB | t | reset_token | 97.00% | 1401 MB
plan_cancelled_at | 539 MB | f | cancelled_at | 8.30% | 44 MB
users_email | 18 MB | t | email | 28.67% | 5160 kB您可以在这篇博文中阅读更多关于使用 PG Extras 优化 PostgreSQL 性能的信息。
5.更新交易范围
通常推荐的做法是将数据库提交的数量保持在最低限度。这意味着将多个更新查询包装到单个事务中应该可以提高写入性能。
对于许多常见场景，这是一个最佳策略。但是，使用单个事务进行大量数据更新可能会导致所谓的锁问题。那么让我们看看在单个事务中更新超过 100k 行有什么影响：
代码语言：javascript复制UPDATE messages SET status = 'archived';当事务仍处于挂起状态时，您可以使用PG Extraslocks方法（或执行其原始 SQL 源代码）调查它生成的锁。
您可能没有足够大的数据集来locks在更新事务仍在运行时手动执行 SQL。在这种情况下，您可以像这样在单个事务中伪造缓慢的执行时间：
代码语言：javascript复制BEGIN;
UPDATE messages SET status = 'archived';
SELECT pg_sleep(15);
COMMIT;将SQL执行延迟15秒的简单方法
现在，运行locksSQL 应该会返回类似的输出：
代码语言：javascript复制relname | mode | query_snippet
-------------------------------------------------------------------------------
messages | RowExclusiveLock | UPDATE "messages" SET "status" = $1
index_messages_on_status | RowExclusiveLock | UPDATE "messages" SET "status" = $1
index_messages_on_text | RowExclusiveLock | UPDATE "messages" SET "status" = $1
index_messages_on_time | RowExclusiveLock | UPDATE "messages" SET "status" = $1可以看到更新操作获取了RowExclusiveLock并锁定了对应的索引。这意味着在漫长的单事务更新过程中尝试更新相同行的任何其他进程都必须等待它完成。
因此，后台工作进程执行的大规模更新可能会使 Web 服务器进程超时并导致面向用户的应用程序中断。
为避免此问题，您可以使用类似的 SQL 将批处理添加到更新操作：
代码语言：javascript复制UPDATE messages SET status = 'archived'
WHERE id IN
(SELECT ID FROM messages ORDER BY ID LIMIT 10000 OFFSET 0);
UPDATE messages SET status = 'archived'
WHERE id IN
(SELECT ID FROM messages ORDER BY ID LIMIT 10000 OFFSET 10000);
UPDATE messages SET status = 'archived'
WHERE id IN
(SELECT ID FROM messages ORDER BY ID LIMIT 10000 OFFSET 20000);
...上面的示例一次更新 10k 的行。整个操作可能需要比在单个事务中执行更长的时间。但是，每个更新步骤都会快速提交数据库更改，因此其他进程不会卡住。
如果您怀疑您的应用程序的性能因锁定事务而下降，您可以结合使用locksPG blockingExtras 方法来监控长期表锁。
概括
优化 PostgreSQL 的挑战在于，大多数问题只有在数据集和流量足够大的情况下才会出现。在使用小型开发数据库创建新功能时，您不太可能发现潜在的瓶颈。这就是为什么必须监控生产性能并定期深入到 EXPLAIN ANALYZE 输出以保持事情以最佳速度运行的原因。
