btree & hash索引:
digoal=# explain verbose select * from aa order by a limit 10 offset 90; QUERY PLAN ------------------------------------------------------------------------------------- Limit (cost=3.64..4.01 rows=10 width=22) Output: a, b -> Index Scan using aa_pkey on public.aa (cost=0.28..112.30 rows=3001 width=22) Output: a, b (4 rows) digoal=# explain verbose select * from aa where a<2000 order by a limit 10 offset 90; QUERY PLAN ------------------------------------------------------------------------------------ Limit (cost=4.15..4.58 rows=10 width=22) Output: a, b -> Index Scan using aa_pkey on public.aa (cost=0.28..44.13 rows=1020 width=22) Output: a, b Index Cond: (aa.a < 2000) (5 rows)
digoal=# truncate table aa; TRUNCATE TABLE
digoal=# create index idx_b on aa(b); CREATE INDEX
digoal=# insert into aa select generate_series(1,1000),random(); INSERT 0 1000
digoal=# explain analyze select * from aa order by a limit 10 offset 90; QUERY PLAN ------------------------------------------------------------------------------------------------------ Limit (cost=3.64..4.01 rows=10 width=22) (actual time=0.083..0.089 rows=10 loops=1) -> Index Scan using aa_pkey on aa (cost=0.28..112.30 rows=3001 width=22) (actual time=0.034..0.076 rows=100 loops=1) Total runtime: 0.116 ms (3 rows)
gist索引和gin索引
gin索引:
CREATE INDEX name ON table USING gin(column);
Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type.
一个表如果使用了gin索引的时候,那么在insert,update的时候如果数据量一多则会出现插入缓慢的迹象。是因为pg8.4之后使用了fastupdate技术对gin索引优化,提高速度的方法有两个:gist索引:
Creates a GiST (Generalized Search Tree)-based index. The column can be of tsvector or tsquery type.