teledb=# create table t_mul_idx (f1 int,f2 int,f3 int,f4 int);
CREATE TABLE
teledb=# create index t_mul_idx_idx on t_mul_idx(f1,f2,f3); 
CREATE INDEX

多字段使用注意事项：

• or 查询条件 bitmap scan 最多支持两个不同字段条件。

  teledb=# insert into t_mul_idx select t,t,t,t from generate_series(1,1000000) as t;
  INSERT 0 1000000
  teledb=# analyze ;
  ANALYZE
  teledb=# explain select * from t_mul_idx where f1=1 or f2=2 ; 
                                          QUERY PLAN   
  -------------------------------------------------------------------------------------------
   Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
     Node/s: dn001, dn002
     ->  Bitmap Heap Scan on t_mul_idx  (cost=7617.08..7621.07 rows=2 width=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: 5.134 ms
  teledb=# explain select * from t_mul_idx where f1=1 or f2=2 or f1=3 ; 
                                          QUERY PLAN   
  -------------------------------------------------------------------------------------------
   Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
     Node/s: dn001, dn002
     ->  Bitmap Heap Scan on t_mul_idx  (cost=7619.51..7625.49 rows=3 width=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: 5.048 ms
  teledb=# explain select * from t_mul_idx where f1=1 or f2=2 or f3=3 ; 
                               QUERY PLAN   
  --------------------------------------------------------------------
   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..12966.87 rows=3 width=16)
           Filter: ((f1 = 1) OR (f2 = 2) OR (f3 = 3))
  (4 rows)
  
  Time: 3.153 ms
  

• 如果返回字段全部在索引文件中，则只需要扫描索引，IO 开销会更少。

  teledb=# explain select f1,f2,f3 from t_mul_idx where f1=1 ; 
                                          QUERY PLAN  
  -------------------------------------------------------------------------------------------
   Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
     Node/s: dn01
     ->  Index Only Scan using t_mul_idx_idx on t_mul_idx  (cost=0.42..8.44 rows=1 width=12)
           Index Cond: (f1 = 1)
  (4 rows)
  
  Time: 2.630 ms
  

• 更新性能比单字段多索引文件要好。
  • 多字段

    teledb=# truncate table t_mul_idx;
    TRUNCATE TABLE
    Time: 44.930 ms
    teledb=# insert into t_mul_idx select t,t,t,t from generate_series(1,1000000) as t; 
    INSERT 0 1000000
    Time: 1985.901 ms (00:01.986)
    

  • 单字段

    teledb=# create table t_simple_idx (f1 int,f2 int,f3 int,f4 int);
    CREATE TABLE
    teledb=# create index t_simple_idx_idx on t_simple_idx(f1); 
    CREATE INDEX
    teledb=# create index t_simple_idx_idx1 on t_simple_idx(f2); 
    CREATE INDEX
    teledb=# create index t_simple_idx_idx2 on t_simple_idx(f3); 
    CREATE INDEX
    teledb=# insert into t_simple_idx select t,t,t,t from generate_series(1,1000000) as t;
    INSERT 0 1000000
    Time: 4186.401 ms (00:04.186)
    

• 多字段索引走非第一字段查询时性能比独立的单字段差。
  • 多字段

    teledb=# select * from t_mul_idx where f1=1;
     f1 | f2 | f3 | f4 
    ----+----+----+----
      1 |  1 |  1 |  1
    (1 row)
    
    Time: 1.034 ms
    teledb=# select * from t_mul_idx where f2=1;
     f1 | f2 | f3 | f4 
    ----+----+----+----
      1 |  1 |  1 |  1
    (1 row)
    
    Time: 37.072 ms
    teledb=# select * from t_mul_idx where f3=1;
     f1 | f2 | f3 | f4 
    ----+----+----+----
      1 |  1 |  1 |  1
    (1 row)
    
    Time: 36.229 ms
    

  • 单字段

    teledb=# select * from t_simple_idx where f1=1;
     f1 | f2 | f3 | f4 
    ----+----+----+----
      1 |  1 |  1 |  1
    (1 row)
    
    Time: 2.610 ms
    teledb=# select * from t_simple_idx where f2=1;
     f1 | f2 | f3 | f4 
    ----+----+----+----
      1 |  1 |  1 |  1
    (1 row)
    
    Time: 3.266 ms
    teledb=# select * from t_simple_idx where f3=1;
     f1 | f2 | f3 | f4 
    ----+----+----+----
      1 |  1 |  1 |  1
    (1 row)
    
    Time: 1.316 ms