常见的导致PG创建索引慢的原因_创建索引很慢

1.表的大小问题

如果表的数据很多，索引建立在大表上的时候，会导致建索引的时间很慢。所以如果表的数据量很多，可以考虑重新设计表结构或拆分表。

可以考虑使用分区表，使用分区表来分割数据。这样在建立索引时，可以只对需要建立索引的分区进行操作，从而提高索引建立的速度。

2.索引类型

不同类型的索引建立的速度可能会有所不同，因为存储的结构不一致。例如，B-tree索引通常比GiST索引建立得更快。不同的索引策略适用于不同的场景，选择合适的索引策略可以提高建索引的速度和效率。因此，可以考虑更改索引类型，以便更快地建立索引。

3.查看服务器负载

检查系统是否存在其他进程占用了大量的系统资源，如CPU、内存和磁盘IO等。可以使用top和vmstat,iostat等工具查看。

4.磁盘空间

如果磁盘空间不足，可能会导致索引建立速度变慢。因此，可以检查磁盘空间是否充足，并清理不需要的文件。

5.检查并发连接数

如果并发连接数过高，可能会导致建索引的速度变慢。可以调整PG的最大并发连接数参数，以避免过多的连接影响建索引的性能。

6.禁用触发器和约束

在建立索引时，可以考虑禁用触发器和约束，以避免对建立索引的影响。完成索引建立后，再重新启用触发器和约束。

禁用触发器

 ALTER TABLE TABLE_NAME DISABLE TRIGGER trigger_name;

开启触发器

 ALTER TABLE TABLE_NAME ENABLE TRIGGER trigger_name;

禁用/开启一张表上所有触发器

 ALTER TABLE table_name DISABLE TRIGGER all;
 ALTER TABLE table_name ENABLE TRIGGER all;

这里 all 会禁用表上的所有外键，同时也禁用负责验证约束的内部触发器， all 也存在一些限制，就是你必须是超级用户才能执行此操作。

7.通过调整参数来使用更多的内存创建索引

maintenance_work_mem默认值(64MB)是很低的，它是用于维护任务的内存设置，会影响于VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的性能。使用更多的内存空间时创建索引的速度提升会非常明显。在创建索引的时候，它控制构建索引时使用的最大内存量。构建B树索引时，必须对数据排序，如果要排序的数据在maintenance_work_mem内存中放置不下，它将会溢出到磁盘中。

++因此在执行CREATE INDEX命令之前，可以在本地会话中使用SET命令临时增加该值。++

shared_buffers和work_mem参数：shared_buffers决定了PG在内存中缓存数据的大小，work_mem参数决定了其内存中执行sort和hash操作所能使用的内存大小。
如果这两个参数设置不合理，也可能会导致索引建立速度变慢。

8.PG支持并行建索引

在建立索引时可以使用并行建索引的方式，以提高建索引的速度。

在postgreSQL11之后的版本中，新增了并行创建索引的功能，能够提供多核索引创建功能。

并行进程数设置分为两类，第一类是并行查询，并行查询的并行度由 max_parallel_workers_per_gather参数控制，第二类是维护命令(例如 CREATE INDEX)，维护命令的并行度由 max_parallel_maintenance_workers 参数控制。

在postgreSQL中，默认情况下启用并行索引创建。当在使用4个cpu内核的情况下创建索引，且max_parallel_maintenance_workers设置成4的时候，会使用4个核去并行创建。所使用并行数也受限于所拥有的cpu核数，当max_parallel_maintenance_workers设置的值超过了cpu核数，因为其cpu核数并没有多余的供它使用，所以参数多的部分并没有意义。

可通过 ALTER TABLE 方式禁止表上的并行创建索引，如下禁止表t1上的所有并行创建索引

 ALTER TABLE t1 SET (parallel_workers=0);

如果想恢复指定表上的parallel_workers参数设置,可采用如下方式
 

ALTER TABLE t1 RESET(parallel_workers);

9.使用SSD硬盘

SSD硬盘相比传统的机械硬盘具有更快的读写速度，可以显著提高索引建立的速度。

10.检查锁竞争情况

锁竞争可能导致索引建立速度变慢。可以通过pg_locks视图进行检查是否有相关阻塞。

11.使用预热功能

PG支持使用pg_prewarm插件来预加载数据，可以在建立索引之前预先加载数据到内存中，以提高索引建立的速度。

12.使用Partial Index（部分索引)

PG可以针对表列的部分数据建立索引，来减小索引的大小，从而加快创建索引的速率。它是在表的一个子部分上构造的。在建索引时可以设置过滤条件，只对符合条件的数据建立索引。这样可以减少索引的大小和建立索引的时间，从而提高索引建立的速度。举例如下：

 postgres=# create table t1(id int);
    CREATE TABLE
    postgres=# ALTER TABLE t1 RESET(parallel_workers);
    ALTER TABLE
    postgres=# create index t1_idx on t1(id) where id<5;
    CREATE INDEX
    postgres=# \d t1
                     Table "public.t1"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     id     | integer |           |          |
    Indexes:
        "t1_idx" btree (id) WHERE id < 5

13.避免使用大事务

在建索引时，尽量避免使用大事务。大事务会占用大量的系统资源，可能会导致索引建立速度变慢。可以将事务拆分成多个小事务来处理，从而提高建索引的速度和效率。

14.避免同时创建多个索引

多个索引会增加系统负担，可能会导致索引建立速度变慢。可以通过合并索引来优化建索引的速度和效率。此外，因为PG可以在同一列上建立多个索引。这些索引可能仅名字不一样，这个时候，多余的索引既会在DML操作的时候占用资源，也会在对表做操作的时候反馈慢。所以，合理规划索引，不创建重复索引也是比较且容易忽视的一点。