postgresql 索引使用參考

通常在postgresql創(chuàng)建普通索引如下
create index idx_table_name_x1 on table_name(col_name);
reate index 執(zhí)行后在創(chuàng)建索引期間會阻塞 dml，創(chuàng)建索引時加concurrently參數(shù)不阻塞dml。
創(chuàng)建索引：
CREATE INDEX? idx_t_info ON "public".t_info(code);
在線創(chuàng)建索引：
CREATE INDEX concurrently idx_t_info ON "public".t_info(code);
重建單個索引:
REINDEX INDEX my_index;
重建和表my_table有關(guān)的索引
REINDEX TABLE my_table;
重建broken_db數(shù)據(jù)庫下所有的索引:
REINDEX DATABASE broken_db;
創(chuàng)建唯一索引：
CREATE UNIQUE INDEX idx_t_info ON t_info(name1);
函數(shù)索引：
CREATE INDEX concurrently idx_t_info ON t_info(lower(name1));
部分索引
CREATE INDEX concurrently idx_t_info ON t_info(code) where (code >5);

刪除索引：
drop index idx_t_info;
索引重命名：
alter index idx_t_info rename to idx_t_info1;