1、创建索引
CREATE INDEX "索引名" ON "表名" USING btree (字段名);
注:字段的识别度越高,索引越有效。
2、删除索引
drop index 索引名;
3、查询某个表的全部索引
SELECT
A.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
FROM
PG_AM B
LEFT JOIN PG_CLASS F ON B.OID = F.RELAM
LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID
LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID
LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID,
PG_INDEXES A
WHERE
A.SCHEMANAME = E.SCHEMANAME AND A.TABLENAME = E.RELNAME AND A.INDEXNAME = E.INDEXRELNAME
AND E.RELNAME = '表名'
4、查看索引所在表及表和索引的大小
SELECT
relname AS TABLE_NAME,
pg_size_pretty (pg_relation_size(relid)) table_size,
indexrelname index_name,
pg_size_pretty (
pg_relation_size (indexrelid)
) index_size,
idx_scan
FROM
pg_stat_user_indexes
WHERE
schemaname = 'rica'
ORDER BY
pg_relation_size (indexrelid) DESC;
5、查看表大小及索引大小
SELECT
TABLE_NAME AS table_full_name,
pg_size_pretty (
pg_relation_size (
'"' || table_schema || '"."' || TABLE_NAME || '"'
)
) AS table_size,
pg_size_pretty (
pg_total_relation_size (
'"' || table_schema || '"."' || TABLE_NAME || '"'
)
) AS total_size
FROM
information_schema.tables
WHERE
table_schema = 'schema名字'
ORDER BY
table_full_name ASC
6、重建索引
REINDEX INDEX 索引名字;
重建慢的原因:有可能autovacuum在对这表进行操作,执行reindex就会等vacuum完成
vacuum:运行数据库维护的对表和索引进行垃圾回收的进程
7、select时,强行走索引
select /*+ index(T 索引名) */ * from 表名 T where T.字段名 = 'XXX'
注: