PostgreSQL的查询计划器充满了惊喜，所以用常识性的方法来编写高性能的查询有时会产生误导。在这篇博文中，我将描述在EXPLAIN ANALYZE和Postgres元数据分析的帮助下，优化看似明显的查询的例子。
所有的测试查询都是在PostgreSQL 12上进行的，表的种子为100万个对象。如果你想用一个较小的开发数据集来复制类似的行为，你就必须通过运行来阻止使用顺序扫描。
SET enable_seqscan TO off;
本教程假定对阅读EXPLAIN ANALYZE报告有一些基本的熟悉。你可以查看这篇博文来了解这个主题的介绍。
1.通过一个函数调用进行搜索
通过使用PostgreSQL函数调用修改的值来搜索是很常见的。让我们看一下通过小写的值搜索一个列的查询计划。
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
EXPLAIN ANALYZE 为简洁起见，输出已被截断。
报告显示，查询计划器执行一个低效的Seq Scan 和Filter 操作来执行查询。由于在我们的查询中添加了BUFFERS 选项，我们可以看到数据库不得不使用缓慢的磁盘读取操作来获取超过4万个数据页，而其中只有约1k被缓存在内存中。
一个通过函数搜索的查询不能使用标准索引。所以你需要添加一个自定义索引，这样才有效率。但是，在每个查询的基础上添加自定义索引并不是一个非常可扩展的方法。你可能会发现自己有多个冗余的索引，大大降低了写操作的速度。
如果大写字母和小写字母不重要，你可以运行迁移到小写所有的值，并使标准索引工作。然而，如果你仍然想在你的数据库中存储大写字母，你可以考虑使用CITEXT扩展。它创建了一个不区分大小写的列，可以在不创建自定义索引的情况下有效地进行搜索。
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
原始查询的180ms执行时间可能看起来不多。但我们刚刚成功地将它的速度提高了几个数量级，降到了1ms以下!无论数据大小如何，新的解决方案都能保持高性能，而且查询只从内存缓存中获取三个缓冲区块。另外，通过利用扩展，我们可以避免添加额外的索引。
2.2.通过一个模式进行查询
LIKE 和 查询经常被使用，但并不总是很明显，额外的设置对于有效执行它们是必要的。让我们看看一个样本查询在标准的B树索引下是如何表现的。ILIKE
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 Scan 和Filter 。
为了使这个查询的速度加快，我们必须添加一个自定义的扩展和索引类型。运行以下命令。
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
除非某列被配置为NOT NULL ，否则在使用它进行排序时必须要小心。默认的ASC 顺序将总是在结果的最后返回NULL 值。但是，如果你想按降序对潜在的NULL 字符串进行排序，但在最后保留所有的NULL？一个初步的方法可能是利用NULLS LAST 自定义排序顺序。让我们仔细看看这种查询会产生的EXPLAIN ANALYZE 输出。
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 值开始。因此，如果我们想把它们移到响应的最后，我们可以这样改写。
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秒钟。尽管email 列是有索引的，但标准索引不能用于用NULLS LAST 选项进行排序。相反，数据库必须在内存中对整个表进行排序，或者退回到更慢的磁盘上的排序。这不仅降低了性能，而且还可能大大增加整个内存的使用。
你可以通过添加一个自定义的NULLS LAST 索引来解决这个问题，如PostgreSQL文档中所描述的。但是，就像通过函数搜索的情况一样，在每个查询的基础上添加自定义索引是一种不好的做法。
得到所需结果的一个简单方法是写两个查询。第一个将获取排序后的非空值。如果结果不满足LIMIT ，另一个查询会获取剩余的具有NULL 的记录。
SELECT *
FROM users ORDER BY email DESC
WHERE email IS NOT NULL LIMIT 10;
SELECT *
FROM users
WHERE email IS NULL LIMIT 10;
4.臃肿null_indexes
正如我们在前面的例子中所确定的，添加一个正确的索引可以大大改善查询的执行时间。然而，过度使用索引会极大地膨胀你的数据库的大小，并增加维护内存的使用。此外，索引必须在每次写操作时被更新。因此，限制它们的数量和范围通常是一个好办法。
你的数据库很可能有一些所谓的*（被我称为）"NULL索引"。*这些是包含高比例的NULL 值的索引。
根据业务逻辑，NULL 值可能被用于搜索，所以这些索引是正确的。但是，通常情况下，你不会写一个查询来搜索包含特定NULL 值的行。如果是这种情况，重新创建索引以排除NULL，将减少磁盘的使用，并限制其更新的频率。
你可以运行下面的命令来删除和重建一个索引，只包括NOT NULL 行。
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源代码），看看你的数据库是否有许多可以修剪的索引，以及预期节省的磁盘空间是多少。
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 Extrasnull_indexes 方法的输出。
你可以在这篇博文中阅读更多关于用PG Extras优化PostgreSQL性能的信息。
5.更新事务范围
通常推荐的做法是将数据库提交的数量保持在最低水平。这意味着将多个更新查询包裹在一个事务中应该可以提高写入性能。
对于许多常见的场景，这是一个最佳策略。但是，使用单个事务进行大规模的数据更新可能会引起所谓锁的问题。因此，让我们看看在单个事务中更新超过100k行会有什么影响。
UPDATE messages SET status = 'archived';
当事务还在等待时，你可以通过使用PG Extraslocks 方法（或执行其原始SQL源代码）调查它产生了哪些锁。
你可能没有足够大的数据集来手动执行locks SQL，而更新事务仍在运行。在这种情况下，你可以在这样的单一事务内伪造缓慢的执行时间。
BEGIN;
UPDATE messages SET status = 'archived';
SELECT pg_sleep(15);
COMMIT;
一个简单的方法来延迟SQL执行15秒
现在，运行locks SQL应该返回类似的输出。
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。
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的批次来更新行。整个操作可能会比在单个事务中进行的时间要长。但是，每个更新步骤都会快速提交数据库变化，所以其他进程不会被卡住。
如果你怀疑你的应用程序的性能会因为锁定事务而下降，你可以使用 locks和 blockingPG Extras的方法来监控持久的表锁。
总结
优化PostgreSQL的挑战是，大多数问题只有在有足够大的数据集和流量时才会出现。当用一个小的开发数据库创建一个新功能时，你不可能发现潜在的瓶颈。这就是为什么必须监测生产性能，并定期深入研究EXPLAIN ANALYZE输出，以保持事物以最佳速度运行。
