合 PG中的include索引
Tags： PGPostgreSQL索引include索引
简介
从PostgreSQL 11 开始支持include索引，"include" 索引是一种特殊类型的索引，它允许将非键列（non-key columns）添加到索引中，以提高查询性能。通常情况下，索引只包含键列的值，而非键列的值需要从表中读取。但是，通过使用 "include" 索引，可以将非键列的值直接存储在索引中，从而避免了额外的表访问。
以下是 "include" 索引的一些重要特点：
- 非键列的存储："include" 索引允许在索引中包含非键列的值。这些非键列的值将作为附加信息存储在索引中，而不需要从表中读取。这样可以减少磁盘IO和内存消耗。
- 提高查询性能：通过将经常使用的非键列添加到 "include" 索引中，可以提高查询性能。查询可以直接从索引中获取所需的列值，而无需访问表数据。
- 索引大小和性能权衡：添加非键列会增加索引的大小。因此，需要权衡索引大小和查询性能之间的关系。过大的索引可能会降低写操作的性能，因为需要维护更大的索引结构。
- 索引限制：并非所有的列都适合放入 "include" 索引中。通常适合包含在 "include" 索引中的列是频繁使用的、较小的列，以及那些在查询中被用作过滤、排序或返回结果的列。
创建 "include" 索引的语法与创建普通索引类似，只需在创建语句中使用 INCLUDE 关键字，并指定要包含在索引中的非键列。例如：
1 | CREATE INDEX idx_myindex ON mytable (key_column) INCLUDE (included_column1, included_column2); |
在上述示例中，idx_myindex 是一个包含 key_column、included_column1 和 included_column2 的 "include" 索引。
"include" 索引是一个有用的工具，可以根据查询需求来优化索引的设计，提高查询性能，并减少不必要的表访问。但是，它仅适用于特定的使用情况，需要仔细考虑哪些非键列适合包含在索引中，以及索引大小和性能之间的权衡。
虽然 "include" 索引在某些情况下可以提供性能优势，但它也有一些缺点需要考虑：
- 索引大小增加：将非键列包含在索引中会增加索引的大小。较大的索引会占用更多的磁盘空间，并可能导致更多的内存消耗。这对于拥有大量数据和频繁更新的表来说尤为重要。更大的索引可能会导致更长的磁盘IO时间和额外的内存开销。
- 写操作性能下降：由于 "include" 索引的大小增加，写操作（例如插入、更新和删除）的性能可能会受到影响。每次写操作都需要更新索引结构，而较大的索引结构可能会导致更多的IO操作和额外的索引维护开销。因此，在高写入负载的环境中，使用 "include" 索引可能会导致写操作的性能下降。
- 索引维护开销增加：添加非键列到 "include" 索引中会增加索引维护的开销。当表中的数据发生变化时（如插入、更新或删除），PG需要更新索引中的相应值。由于 "include" 索引包含了更多的列，所以索引维护操作可能更复杂且更耗时。
- 查询性能优化受限："include" 索引只对包含在索引中的列提供性能优化。如果查询需要访问的列不在 "include" 索引中，仍然需要从表中读取这些列的值，无法利用索引的优势。因此，在选择 "include" 索引时，需要仔细考虑查询的需求和覆盖索引的能力。
综上所述，使用 "include" 索引需要权衡索引大小、写操作性能和查询性能优化之间的关系。它适用于特定的场景和查询模式，特别是对于经常需要访问和筛选非键列的查询。然而，在高写入负载、大型数据集和不适当的索引设计的情况下，使用 "include" 索引可能导致性能下降和资源消耗增加。因此，在应用中需要仔细评估和测试以确定是否适合使用 "include" 索引。
示例
下面测试下三种索引：普通索引、多列索引、include index三者的读写性能。
创建实验环境
1 2 3 4 5 6 | create table t1 (id int, c1 int, c2 int, c3 int, info text, crt_time timestamp); create table t2(like t1); create table t3(like t1); create index idx_t1_1 on t1 (id) include(c1,c2,c3,info,crt_time); create index idx_t2_1 on t2 (id,c1,c2,c3,info,crt_time); create index idx_t3_1 on t3(id); |
写入性能对比
1、include index
1 2 3 4 5 6 7 | db3=# \timing on Timing is on. db3=# insert into t1 select (1000*random())::int,1,1,1,'test',now() from generate_series(1,10000000); INSERT 0 10000000 Time: 51274.960 ms (00:51.275) db3=# |
2、多列索引
