PostgreSQL 索引操作

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'

        注：