索引的负面影响和相关成本
-
曾经维护过一个系统，单个表有400多个字段，表上面有40多个索引，并且大量的索引是组合索引，日积月累就造成很多无效索引，对于维护重建索引非常耗时
-
索引通常被认为是SQL性能调优的灵丹妙药，且PG数据库支持各种各样的索引来满足不同的场景。我们经常看到许多关于调优的文章和讨论，讨论如何创建索引来加速SQL，但是很少有人讨论删除它们。偶然看到一篇文章发现正是说出了心中想要说的，便记录下来
-
创建越来越多的索引正在对许多系统造成严重损害，很多时候为了系统的稳定性，在考虑任何新索引之前，我们应该首先分析系统已有的索引，删除那些没用的索引，了解索引的后果和开销有助于做出明智的决定，并有可能是系统免于许多潜在的问题。
-
索引不是免费的，这些好处伴随着性能和资源消耗方面的成本，以下是过渡使用索引可能导致的问题，这篇文章是关于PostgreSQL的，但大多数问题也适用于其他数据库系统
危害
指数级的开销
添加索引后，我们可能会看到select 语句的性能有所提高。但性能的提升伴随着同一张表上事务的成本。从概念上讲表上的每个DML都需要更新表的所有索引。尽管有很多优化来减少写放大，但是这是一个相当大的开销。
例如，加入有一张表有五个索引，对表的每个insert都会导致对着5个索引的索引记录的insert。逻辑上，五个索引页面也将被更新，开销是5倍
内存使用
索引必须在内存中，无论是否有任何查询使用它们，因为它们需要由事务更新。实际上，可用于表页的内存变小了。索引越多，有效缓存所需要的内存就越大。如果我们不增加可用内存，就会开始损害系统的整体性能。
随机写：更新索引成本高
与将新记录插入表不同，行不太可能被插入到同一页中。众所周知，像b-tree索引这样的索引会导致更多的随机写入。
索引比表需要更多的缓存
由于随机写入和读取，索引需要更多的页面在缓存中。索引的缓存要求通常比关联表高得多
WAL生成
除了表更新的WAL记录外，还会有索引的WAL记录。这是因为有助于崩溃恢复和复制，如果我们使用pg_gather工具进行分析，WAL生成的开销将清晰可见，实际影响屈居于索引类型
越来越多的I/O
不仅生成WAL记录，也会有更多的页被弄脏，随着索引页变脏，必须要将其写会文件，从而再次导致了更多的I/O，——“ DataFileWrite ”等待事件，如上一个屏幕截图所示。
另一个副作用是索引增加了活动数据集的总大小。“活动数据集”是指经常查询和使用的表和索引。随着活动数据集大小的增加，缓存的效率也越来越低。缓存效率低会导致读取更多的数据文件，因此读取I/O会增加。这是为特定查询从存储中获取额外的索引页所需要的读取I/O的补充。
另一个主要是选择查询的系统的 pg_gather 报告再次显示了这个问题。随着活动数据集的增加，PostgreSQL 别无选择，只能从存储中取出页面。
持续时间较长的 DataFileRead 百分比越大，表明活动数据集越大，不可缓存。
对VACUUM/AUTOVACUUM的影响
开销不仅用于插入或更新索引页。维护也会产生开销，因为索引还需要清理旧的元组引用
我曾见过这样的情况，由于表的大小，而且最重要的是，表上的索引数量过多，单个表上的 autovacuum worker 运行时间很长。事实上，用户经常看到他们的 autovacuum worker 被“卡住”数小时而没有在更长的时间内显示任何进展。
autovacuum操作也会读索引进行清理，可以通过pg_stat_progress_vacuum观察：https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PROGRESS-REPORTING
随着时间的推移，索引会变得臃肿并且效率降低。许多系统可能需要定期索引维护 (REINDEX)。
因小失大
Tunnel vision is the loss of peripheral vision with retention of central vision, resulting in a constricted circular tunnel-like field of vision.[1]
-
有时候我们发现局部有问题，解决局部的问题实际上对整体带来了更大的问题
用户可能专注于特定的SQL语句，试图调整并决定创建索引。通过创建用于调整查询的索引，我们将更多的系统资源转移到该查询。然后可能会引起整体性能下降
随着我们不断创建越来越多的索引来调整其他查询，资源将再次转向其他查询。最终整体的性能逐渐下降，最终每个使用系统的人都会受到伤害。尝试调整的人应该考虑系统的每个部分如何共存（最大化业务价值）而不是特定查询的绝对性能
更大的存储需求
总数据库的大小比实际大，备份需要耗费更多的时间，存储和网络资源，同样的备份会给主机带来更多的负载。这也会增加还原备份和恢复备份的时间。更大的数据库也就需要更多的时间来构建备用实例
索引更容易膨胀
PostgreSQL 14 的静默索引损坏或由于glibc 排序规则更改导致的索引损坏，这些错误时不时地出现并影响许多环境，即使在今天。在使用数据库数十年的时间里，我观察到索引损坏的报告更加频繁。（我希望任何参与 PostgreSQL 多年并见过数百个案例的人都会同意我的看法）。随着索引数量的增加，损坏的概率也在增加
如何做
需要思考的点：
1、是否必须有这个索引或者是否有必要以更多索引为代价来加快速度
2、有没有办法重写查询以获取更好的性能
3、当新增一个索引，是否其他索引就可以删除？
4、对于现有的索引：检查无用的索引，并定期删除（pg_stat_user_indexes中idx_scan为0的索引）。类似pgexperts的脚本可以帮助进行更多分析
即将发布的 PostgreSQL 16 在pg_stat_user_indexes / pg_stat_all_indexes中多了一列， 名称为last_idx_scan，它可以告诉我们最后一次使用索引的时间（时间戳）。这将帮助我们全面了解系统中的所有索引。
总结
用简单的话总结：索引并不便宜，索引是有代价的，而且代价是多方面的，索引并不总是好的，顺序扫描也不总是坏的。避免改进因为改进单个查询而影响到整体性能下降。从调整主机、操作系统、PostgreSQL参数等开始调整系统的自上而下方法会产生更好的结果。再创建索引前进行客观的“成本效益分析”很重要
查询未使用索引：
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;
在 PostgreSQL 中，WAL（Write-Ahead Logging）是一种机制，用于确保数据的持久性和一致性。WALWrite 和 WALSync 都是与 WAL 相关的操作，但它们的作用和含义略有不同。
1、WALWrite
WALWrite 是指将 WAL 缓冲区中的数据写入到 WAL 文件中的操作。当 PostgreSQL 执行写入操作时，数据首先会被写入到 WAL 缓冲区中，然后再由 WALWriter 进程将数据写入到 WAL 文件中。WALWrite 操作的目的是将数据从内存中写入到磁盘中，以确保数据的持久性。
2、WALSync
WALSync 是指将 WAL 文件中的数据同步到磁盘上的操作。当 PostgreSQL 执行 WALWrite 操作时，数据会被写入到 WAL 文件中，但并不一定会立即同步到磁盘上。WALSync 操作的目的是将 WAL 文件中的数据同步到磁盘上，以确保数据的一致性和可靠性。
因此，WALWrite 和 WALSync 的区别在于它们的作用和含义不同。WALWrite 是将数据从内存中写入到 WAL 文件中，而 WALSync 是将 WAL 文件中的数据同步到磁盘上。WALWrite 操作通常比 WALSync 操作更频繁，因为 WAL 缓冲区中的数据需要定期写入到 WAL 文件中，而 WAL 文件中的数据则需要在适当的时候同步到磁盘上。
需要注意的是，WALWrite 和 WALSync 操作都会对系统的性能产生影响，因为它们都需要进行磁盘 I/O 操作。为了提高系统的性能和稳定性，可以采取一些措施，例如调整 WAL 缓冲区的大小、调整 WALSync 的频率、使用更快的磁盘等
参考资料：
https://www.percona.com/blog/postgresql-indexes-can-hurt-you-negative-effects-and-the-costs-involved/
https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql
https://blog.csdn.net/dazuiba008/article/details/121378955
https://github.com/jobinau/pg_gather
https://www.migops.com/blog/important-postgresql-14-update-to-avoid-silent-corruption-of-indexes/
索引的负面影响和相关成本
-
曾经维护过一个系统，单个表有400多个字段，表上面有40多个索引，并且大量的索引是组合索引，日积月累就造成很多无效索引，对于维护重建索引非常耗时
-
索引通常被认为是SQL性能调优的灵丹妙药，且PG数据库支持各种各样的索引来满足不同的场景。我们经常看到许多关于调优的文章和讨论，讨论如何创建索引来加速SQL，但是很少有人讨论删除它们。偶然看到一篇文章发现正是说出了心中想要说的，便记录下来
-
创建越来越多的索引正在对许多系统造成严重损害，很多时候为了系统的稳定性，在考虑任何新索引之前，我们应该首先分析系统已有的索引，删除那些没用的索引，了解索引的后果和开销有助于做出明智的决定，并有可能是系统免于许多潜在的问题。
-
索引不是免费的，这些好处伴随着性能和资源消耗方面的成本，以下是过渡使用索引可能导致的问题，这篇文章是关于PostgreSQL的，但大多数问题也适用于其他数据库系统
危害
指数级的开销
添加索引后，我们可能会看到select 语句的性能有所提高。但性能的提升伴随着同一张表上事务的成本。从概念上讲表上的每个DML都需要更新表的所有索引。尽管有很多优化来减少写放大，但是这是一个相当大的开销。
例如，加入有一张表有五个索引，对表的每个insert都会导致对着5个索引的索引记录的insert。逻辑上，五个索引页面也将被更新，开销是5倍
内存使用
索引必须在内存中，无论是否有任何查询使用它们，因为它们需要由事务更新。实际上，可用于表页的内存变小了。索引越多，有效缓存所需要的内存就越大。如果我们不增加可用内存，就会开始损害系统的整体性能。
随机写：更新索引成本高
与将新记录插入表不同，行不太可能被插入到同一页中。众所周知，像b-tree索引这样的索引会导致更多的随机写入。
索引比表需要更多的缓存
由于随机写入和读取，索引需要更多的页面在缓存中。索引的缓存要求通常比关联表高得多
WAL生成
除了表更新的WAL记录外，还会有索引的WAL记录。这是因为有助于崩溃恢复和复制，如果我们使用pg_gather工具进行分析，WAL生成的开销将清晰可见，实际影响屈居于索引类型
越来越多的I/O
不仅生成WAL记录，也会有更多的页被弄脏，随着索引页变脏，必须要将其写会文件，从而再次导致了更多的I/O，——“ DataFileWrite ”等待事件，如上一个屏幕截图所示。
另一个副作用是索引增加了活动数据集的总大小。“活动数据集”是指经常查询和使用的表和索引。随着活动数据集大小的增加，缓存的效率也越来越低。缓存效率低会导致读取更多的数据文件，因此读取I/O会增加。这是为特定查询从存储中获取额外的索引页所需要的读取I/O的补充。
另一个主要是选择查询的系统的 pg_gather 报告再次显示了这个问题。随着活动数据集的增加，PostgreSQL 别无选择，只能从存储中取出页面。
持续时间较长的 DataFileRead 百分比越大，表明活动数据集越大，不可缓存。
对VACUUM/AUTOVACUUM的影响
开销不仅用于插入或更新索引页。维护也会产生开销，因为索引还需要清理旧的元组引用
我曾见过这样的情况，由于表的大小，而且最重要的是，表上的索引数量过多，单个表上的 autovacuum worker 运行时间很长。事实上，用户经常看到他们的 autovacuum worker 被“卡住”数小时而没有在更长的时间内显示任何进展。
autovacuum操作也会读索引进行清理，可以通过pg_stat_progress_vacuum观察：https://www.postgresql.org/docs/current/progress-reporting.html#VACUUM-PROGRESS-REPORTING
随着时间的推移，索引会变得臃肿并且效率降低。许多系统可能需要定期索引维护 (REINDEX)。
因小失大
Tunnel vision is the loss of peripheral vision with retention of central vision, resulting in a constricted circular tunnel-like field of vision.[1]
-
有时候我们发现局部有问题，解决局部的问题实际上对整体带来了更大的问题
用户可能专注于特定的SQL语句，试图调整并决定创建索引。通过创建用于调整查询的索引，我们将更多的系统资源转移到该查询。然后可能会引起整体性能下降
随着我们不断创建越来越多的索引来调整其他查询，资源将再次转向其他查询。最终整体的性能逐渐下降，最终每个使用系统的人都会受到伤害。尝试调整的人应该考虑系统的每个部分如何共存（最大化业务价值）而不是特定查询的绝对性能
更大的存储需求
总数据库的大小比实际大，备份需要耗费更多的时间，存储和网络资源，同样的备份会给主机带来更多的负载。这也会增加还原备份和恢复备份的时间。更大的数据库也就需要更多的时间来构建备用实例
索引更容易膨胀
PostgreSQL 14 的静默索引损坏或由于glibc 排序规则更改导致的索引损坏，这些错误时不时地出现并影响许多环境，即使在今天。在使用数据库数十年的时间里，我观察到索引损坏的报告更加频繁。（我希望任何参与 PostgreSQL 多年并见过数百个案例的人都会同意我的看法）。随着索引数量的增加，损坏的概率也在增加
如何做
需要思考的点：
1、是否必须有这个索引或者是否有必要以更多索引为代价来加快速度
2、有没有办法重写查询以获取更好的性能
3、当新增一个索引，是否其他索引就可以删除？
4、对于现有的索引：检查无用的索引，并定期删除（pg_stat_user_indexes中idx_scan为0的索引）。类似pgexperts的脚本可以帮助进行更多分析
即将发布的 PostgreSQL 16 在pg_stat_user_indexes / pg_stat_all_indexes中多了一列， 名称为last_idx_scan，它可以告诉我们最后一次使用索引的时间（时间戳）。这将帮助我们全面了解系统中的所有索引。
总结
用简单的话总结：索引并不便宜，索引是有代价的，而且代价是多方面的，索引并不总是好的，顺序扫描也不总是坏的。避免改进因为改进单个查询而影响到整体性能下降。从调整主机、操作系统、PostgreSQL参数等开始调整系统的自上而下方法会产生更好的结果。再创建索引前进行客观的“成本效益分析”很重要
查询未使用索引：
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;
在 PostgreSQL 中，WAL（Write-Ahead Logging）是一种机制，用于确保数据的持久性和一致性。WALWrite 和 WALSync 都是与 WAL 相关的操作，但它们的作用和含义略有不同。
1、WALWrite
WALWrite 是指将 WAL 缓冲区中的数据写入到 WAL 文件中的操作。当 PostgreSQL 执行写入操作时，数据首先会被写入到 WAL 缓冲区中，然后再由 WALWriter 进程将数据写入到 WAL 文件中。WALWrite 操作的目的是将数据从内存中写入到磁盘中，以确保数据的持久性。
2、WALSync
WALSync 是指将 WAL 文件中的数据同步到磁盘上的操作。当 PostgreSQL 执行 WALWrite 操作时，数据会被写入到 WAL 文件中，但并不一定会立即同步到磁盘上。WALSync 操作的目的是将 WAL 文件中的数据同步到磁盘上，以确保数据的一致性和可靠性。
因此，WALWrite 和 WALSync 的区别在于它们的作用和含义不同。WALWrite 是将数据从内存中写入到 WAL 文件中，而 WALSync 是将 WAL 文件中的数据同步到磁盘上。WALWrite 操作通常比 WALSync 操作更频繁，因为 WAL 缓冲区中的数据需要定期写入到 WAL 文件中，而 WAL 文件中的数据则需要在适当的时候同步到磁盘上。
需要注意的是，WALWrite 和 WALSync 操作都会对系统的性能产生影响，因为它们都需要进行磁盘 I/O 操作。为了提高系统的性能和稳定性，可以采取一些措施，例如调整 WAL 缓冲区的大小、调整 WALSync 的频率、使用更快的磁盘等
参考资料：
https://www.percona.com/blog/postgresql-indexes-can-hurt-you-negative-effects-and-the-costs-involved/
https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql
https://blog.csdn.net/dazuiba008/article/details/121378955
https://github.com/jobinau/pg_gather
https://www.migops.com/blog/important-postgresql-14-update-to-avoid-silent-corruption-of-indexes/