多字段索引
最近更新时间: 2025-02-18 16:02:00
postgres=# create table t_mul_idx (f1 int,f2 int,f3 int,f4 int);
CREATE TABLE
Time: 308.109 ms
postgres=# create index t_mul_idx_idx on t_mul_idx(f1,f2,f3);
CREATE INDEX
Time: 108.734 ms多字段使用注意事项：
- or查询条件bitmapscan最多支持两个不同字段条件。
postgres=# insert into t_mul_idx select t,t,t,t from generate_series(1,1000000) as t;
INSERT 0 1000000
postgres=# analyze ;
ANALYZE
postgres=# explain select * from t_mul_idx where f1=1 or f2=2;
QUERYPLAN
-------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Bitmap Heap Scan ont_mul_idx (cost=7617.08..7621.07 rows=2width=16)
Recheck Cond: ((f1 = 1) OR (f2 = 2))
-> BitmapOr (cost=7617.08..7617.08 rows=2 width=0)
-> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..2.43 rows=1 width=0)
Index Cond: (f1 = 1)
-> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..7614.65 rows=1 width=0)
Index Cond: (f2 = 2)
(9 rows)
Time: 3.655 ms
postgres=# explain select * from t_mul_idxwhere f1=1 or f2=2 or f1=3;
QUERYPLAN
-------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Bitmap Heap Scan ont_mul_idx (cost=7619.51..7625.49 rows=3width=16)
Recheck Cond: ((f1 = 1) OR (f2 = 2) OR (f1 = 3))
-> BitmapOr (cost=7619.51..7619.51 rows=3 width=0)
-> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..2.43 rows=1 width=0)
Index Cond: (f1 = 1)
-> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..7614.65 rows=1 width=0)
Index Cond: (f2 = 2)
-> Bitmap Index Scan on t_mul_idx_idx (cost=0.00..2.43 rows=1 width=0)
Index Cond: (f1 = 3)
(11 rows)
Time: 3.429 ms
postgres=# explain select * from t_mul_idxwhere f1=1 or f2=2 or f3=3;
QUERYPLAN
--------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Seq Scan on t_mul_idx (cost=0.00..12979.87 rows=3 width=16)
Filter: ((f1 = 1) OR (f2 = 2) OR (f3 = 3))
(4 rows)
Time: 1.679 ms- 如果返回字段全部在索引文件中，则只需要扫描索引，io开销会更少。
postgres=# explain select f1,f2,f3 from t_mul_idx where f1=1;
QUERYPLAN
-------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001
-> Index Only Scan usingt_mul_idx_idx on t_mul_idx (cost=0.42..4.44 rows=1 width=12)
Index Cond: (f1 = 1)
(4 rows)
Time: 1.564 ms- 更新性能比单字段多索引文件要好。
--多字段
postgres=# insert into t_simple_idx select t,t,t,t from generate_series(1,1000000) as t;
INSERT 0 1000000
Time: 7143.754 ms (00:07.144)
--单字段
postgres=# insert into t_mul_idx select t,t,t,t from generate_series(1,1000000) as t;
INSERT 0 1000000
Time: 4034.208 ms (00:04.034)- 多字段索引走非第一字段查询时性能比独立的单字段差。
- 多字段
postgres=# select * from t_mul_idx where f1=1;
f1 |f2 | f3 | f4
----+----+----+----
1| 1 | 1 | 1
(1 row)
Time: 1.769 ms
postgres=# select * from t_mul_idx where f2=1;
f1 |f2 | f3 | f4
----+----+----+----
1| 1 | 1 | 1
(1 row)
Time: 25.423 ms
postgres=# select * from t_mul_idx where f3=1;
f1 |f2 | f3 | f4
----+----+----+----
1| 1 | 1 | 1
(1 row)
Time: 27.791 ms
--独立字段
postgres=# select * from t_simple_idx where f1=1;
f1 |f2 | f3 | f4
----+----+----+----
1| 1 | 1 | 1
(1 row)
Time: 1.530 ms
postgres=# select * from t_simple_idx where f2=1;
f1 |f2 | f3 | f4
----+----+----+----
1| 1 | 1 | 1
(1 row)
Time: 2.315 ms
postgres=# select * from t_simple_idx where f3=1;
f1 |f2 | f3 | f4
----+----+----+----
1| 1 | 1 | 1
(1 row)
Time: 2.390 ms