postgresql中的索引

create table t(k serial primary key, v integer);

这个语句会生成一个序列和一个索引，真实的sql语句如下：

CREATE TABLE public.t
(
  k integer NOT NULL DEFAULT nextval('t_k_seq'::regclass),
  v integer,
  CONSTRAINT t_pkey PRIMARY KEY (k)
)
WITH (
  OIDS=FALSE
);

看到生成了t_k_seq的序列和t_pkey的约束

在v上创建索引

create index i on t (v);

explain analyze select count(*) from t where k>9000 and v=5;

"Aggregate  (cost=808.92..808.93 rows=1 width=0) (actual time=3.542..3.542 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on t  (cost=190.67..785.82 rows=9239 width=0) (actual time=1.038..3.025 rows=9189 loops=1)"
"        Recheck Cond: (v = 5)"
"        Filter: (k > 9000)"
"        Rows Removed by Filter: 919"
"        Heap Blocks: exact=443"
"        ->  Bitmap Index Scan on i  (cost=0.00..188.36 rows=10143 width=0) (actual time=0.785..0.785 rows=10108 loops=1)"
"              Index Cond: (v = 5)"
"Planning time: 0.095 ms"
"Execution time: 3.603 ms"

create index i_combine on t(k,v);

执行上面的语句后，执行计划走的是一样的，

explain analyze select count(*) from t where k=9000 and v=5;

这个的执行计划用到了新建的组合索引

"Aggregate  (cost=8.31..8.32 rows=1 width=0) (actual time=0.044..0.044 rows=1 loops=1)"
"  ->  Index Only Scan using i_combine on t  (cost=0.29..8.31 rows=1 width=0) (actual time=0.041..0.041 rows=0 loops=1)"
"        Index Cond: ((k = 9000) AND (v = 5))"
"        Heap Fetches: 0"
"Planning time: 0.340 ms"
"Execution time: 0.076 ms"

以yaml方式查看执行计划

explain (format yaml)select * from customers where customerid>1000 order by zip;

要查看缓冲区中的信息

explain (analyze on,buffers on) select count(*) from t where v=5;

"Aggregate  (cost=786.05..786.06 rows=1 width=0) (actual time=2.885..2.885 rows=1 loops=1)"
"  Buffers: shared hit=473"
"  ->  Bitmap Heap Scan on t  (cost=190.90..760.69 rows=10143 width=0) (actual time=0.765..2.328 rows=10108 loops=1)"
"        Recheck Cond: (v = 5)"
"        Heap Blocks: exact=443"
"        Buffers: shared hit=473"
"        ->  Bitmap Index Scan on i  (cost=0.00..188.36 rows=10143 width=0) (actual time=0.699..0.699 rows=10108 loops=1)"
"              Index Cond: (v = 5)"
"              Buffers: shared hit=30"
"Planning time: 0.090 ms"
"Execution time: 2.929 ms"

explain analyze select count(*) from tt where v  is null;

"Aggregate  (cost=8.27..8.28 rows=1 width=0) (actual time=0.092..0.092 rows=1 loops=1)"
"  ->  Index Only Scan using it_v on tt  (cost=0.29..8.27 rows=1 width=0) (actual time=0.086..0.088 rows=1 loops=1)"
"        Index Cond: (v IS NULL)"
"        Heap Fetches: 1"
"Planning time: 0.070 ms"
"Execution time: 0.117 ms"

排序索引

create index i_order on t(v desc nulls first);

部分索引：只对部分的记录进行索引

函数索引：同oracle

聚集索引：待了解

pg中的索引，可以在同一个列上定义2个索引，这样在重建索引的时候，可以按下面的方式去执行：

create table test(id integer primary key, category text,value text);
create index  test_category_idx on test(category);
create index concurrently new_index on test(category);
begin;
drop index test_category_idx;
alter index new_index rename to test_category_idx;
commit;

create index concurrently 允许索引在被创建的时候执行dml语句

查看未使用的索引

select schemaname,relname,indexrelname,idx_scan from pg_stat_user_indexs order by idx_scan;

重建索引
1在操作系统上使用reindexdb -u postgres -d postgres

为了避免删除索引后，发现索引还有用，能够快速的让索引正常工作，我们可以使用下面的方式来操作

1CREATE OR REPLACE FUNCTION trial_drop_index(iname TEXT)
RETURNS VOID
LANGUAGE SQL AS $$
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = $1::regclass;
$$;

运行这个函数来模拟删除索引，如果发现索引还有用，使用下面的函数恢复索引

CREATE OR REPLACE FUNCTION trial_undrop_index(iname TEXT)
RETURNS VOID
LANGUAGE SQL AS $$
UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = $1::regclass;
$$;

这个就是设置索引是否有效，与oracle是一样的

重命名索引

alter index idx_contacts_name rename to idx_contacts_name_old;

alter index idx_contacts_name_old set tablespace tbs_data01;

将索引的填充因子设置成默认值

alter index idx_contacts_name reset(fillfactor);

查看索引信息

\d+ idx_contacts_name_old