索引类似于书籍的目录，方便对表中的记录快速查找或者排序，但是建索引会有以下代价：
- 增减数据库的存储空间
- 在插入和更新数据时，所以也会被更新
索引的分类
- BTree：PG默认的索引类型，适合等值和范围查询
- Hash：只能处理简单的等职查询
- Gist:不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略
索引创建语法
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE ( column_name [, ...] ) ] [ NULLS [ NOT ] DISTINCT ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ] URL: https://www.postgresql.org/docs/16/sql-createindex.html
通常创建索引时，会把表的数据都读一遍，创建索引的时间长短一般和表的大小有关，创建索引时，不影响对表的查询，但是会阻塞对表的增删改操作。但是pg提供了一种并发创建索引的方法。
索引创建示例
postgres=# create table contacts(id int not null primary key,name varchar(40),phone varchar(32)[],addres text); #创建一张表 postgres=# create index idx_name on contacts(name); # 给字段name添加一个BTree索引。 CREATE INDEX postgres=# postgres=# postgres=# \d contacts; Table "public.contacts" Column | Type | Collation | Nullable | Default ---------+-------------------------+-----------+----------+--------- id | integer | | not null | name | character varying(40) | | | phone | character varying(32)[] | | | address | text | | | Indexes: "contacts_pkey" PRIMARY KEY, btree (id) "idx_name" btree (name) -- 这里可以查到
创建索引的时候可以指定存储参数“WITH (storage_parameter=value)”，常用的存储参数为FILLFACTOR，比如，可以这样创建索引
CREATE INDEX idx_contacts_name on contacts(name) WITH (FILLFACTOR=50);
也可以按降序创建索引：
CREATE INDEX idx_contacts_name on contacts(name desc);
如果字段“name”中有空值，则可以在创建索引时指定空值排在非空值前面：
CREATE INDEX idx_contacts_name on contacts(name DESC NULLS FIRST);
也可以指定空值排在非空值后面：
CREATE INDEX idx_contacts_name on contacts(name DESC NULLS LAST);
并发创建索引
启用并发创建索引时，因为需要对表进行两次扫描，所以可能创建的时间会更长，并发创建索引可以加参数：CONCURRENTLY 来实现。
postgres=# create table testdb(id int not null primary key,note text); CREATE TABLE postgres=# postgres=# create index concurrently idx_testdb_note on testdb(note); -- 注意这里的参数 concurrently CREATE INDEX
并发创建索引的时候需要注意，如果在索引创建过程中被强行取消可能会留下一个无效的索引，这个索引仍然会导致更新速度变慢。如果所创建的是一个唯一索引，这个无效的索引还会导致插入重复值失败
修改索引
语法：
Syntax: ALTER INDEX [ IF EXISTS ] name RENAME TO new_name ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name ALTER INDEX name ATTACH PARTITION index_name ALTER INDEX name [ NO ] DEPENDS ON EXTENSION extension_name ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] ) ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] ) ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number SET STATISTICS integer ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ] URL: https://www.postgresql.org/docs/16/sql-alterindex.html
示例：
postgres=# alter index idx_name rename to idx_name_old; -- 重命名索引 ALTER INDEX postgres=# \d contacts Table "public.contacts" Column | Type | Collation | Nullable | Default ---------+-------------------------+-----------+----------+--------- id | integer | | not null | name | character varying(40) | | | phone | character varying(32)[] | | | address | text | | | Indexes: "contacts_pkey" PRIMARY KEY, btree (id) "idx_name_old" btree (name) postgres=# postgres=# alter index idx_name_old set(fillfactor=70); -- 修改索引的填充因子 ALTER INDEX postgres=# alter index idx_name_old reset(fillfactor); -- 充值索引的填充因子 ALTER INDEX postgres=#
删除索引
语法：
Syntax: DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] URL: https://www.postgresql.org/docs/16/sql-dropindex.html
如果索引“idx_contacts_name_old”存在则删除，如果不存在也不报错(在pg16上如果索引不存在会报错)：
删除索引时，默认使用选项“RESTRICT”，所以加不加关键字“RESTRICT”效果都是一样的，如果有对象依赖该索引，则会删除失败，而使用CASCADE选项表示当有依赖这个索引的对象时，一并把这些对象删除掉，如外键约束