postgresql 索引使用

今天给表加索引，遇到一个情况，表parent_children_relation中常用的sql语句有：

select * from parent_children_relation where parent_account='jz41030110000775';

select * from parent_children_relation where children_account='41030110000775';

select * from parent_children_relation where parent_account='jz41030110000775' and children_account='41030110000775';

这该如何建立索引呢？是在parent_account、children_account列上单独建立2个索引还是在这两个字段上建立一个复合索引呢？

我做个试验：

方案一：

CREATE INDEX parent_children_relation_parent_account_index ON parent_children_relation (parent_account);

CREATE INDEX parent_children_relation_children_account_index ON parent_children_relation (children_account);

select * from parent_children_relation where parent_account='jz41030110000775';

Index Scan using parent_children_relation_parent_account_index on public.parent_children_relation  (cost=0.00..11.96 rows=2 width=43) (actual time=0.103..0.106 rows=1 loops=1)

  Output: id, parent_account, parent_type, children_account

  Index Cond: ((parent_children_relation.parent_account)::text = 'jz41030110000775'::text)

  Buffers: shared hit=1 read=4

Total runtime: 0.138 ms

select * from parent_children_relation where children_account='41030110000775';

Index Scan using parent_children_relation_children_account_index on public.parent_children_relation  (cost=0.00..11.96 rows=2 width=43) (actual time=0.123..0.126 rows=1 loops=1)

  Output: id, parent_account, parent_type, children_account

  Index Cond: ((parent_children_relation.children_account)::text = '41030110000775'::text)

  Buffers: shared hit=1 read=4

Total runtime: 0.158 ms

select * from parent_children_relation where parent_account='jz41030110000775' and children_account='41030110000775';

Index Scan using parent_children_relation_parent_account_index on public.parent_children_relation  (cost=0.00..11.97 rows=1 width=43) (actual time=0.032..0.035 rows=1 loops=1)

  Output: id, parent_account, parent_type, children_account

  Index Cond: ((parent_children_relation.parent_account)::text = 'jz41030110000775'::text)

  Filter: ((parent_children_relation.children_account)::text = '41030110000775'::text)

  Buffers: shared hit=4

Total runtime: 0.064 ms

方案二：

先执行ANALYZE。

CREATE INDEX parent_children_relation_parent_children_account_index ON parent_children_relation (parent_account,children_account);

select * from parent_children_relation where parent_account='jz41030110000775';

Bitmap Heap Scan on public.parent_children_relation  (cost=4.28..11.93 rows=2 width=43) (actual time=0.021..0.022 rows=1 loops=1)

  Output: id, parent_account, parent_type, children_account

  Recheck Cond: ((parent_children_relation.parent_account)::text = 'jz41030110000775'::text)

  Buffers: shared hit=4

  ->  Bitmap Index Scan on parent_children_relation_parent_children_account_index  (cost=0.00..4.28 rows=2 width=0) (actual time=0.016..0.016 rows=1 loops=1)

        Index Cond: ((parent_children_relation.parent_account)::text = 'jz41030110000775'::text)

        Buffers: shared hit=3

Total runtime: 0.044 ms

select * from parent_children_relation where children_account='41030110000775';

Seq Scan on public.parent_children_relation  (cost=0.00..1013.55 rows=2 width=43) (actual time=0.009..4.717 rows=1 loops=1)

  Output: id, parent_account, parent_type, children_account

  Filter: ((parent_children_relation.children_account)::text = '41030110000775'::text)

  Buffers: shared hit=521

Total runtime: 4.731 ms

select * from parent_children_relation where parent_account='jz41030110000775' and children_account='41030110000775';

Index Scan using parent_children_relation_parent_children_account_index on public.parent_children_relation  (cost=0.00..8.28 rows=1 width=43) (actual time=0.016..0.017 rows=1 loops=1)

  Output: id, parent_account, parent_type, children_account

  Index Cond: (((parent_children_relation.parent_account)::text = 'jz41030110000775'::text) AND ((parent_children_relation.children_account)::text = '41030110000775'::text))

  Buffers: shared hit=4

Total runtime: 0.032 ms

看来复合索引(parent_account,children_account)在查询条件是children_account时不走索引，所以决定选方案一。

还遇到一个情况：

update statistical_info set is_receive=TRUE where message_id='10556434' and message_source='1' and receiver_id='858582';

已经在（message_id， message_source）建立索引，查看执行计划

Update on public.statistical_info  (cost=0.00..8.27 rows=1 width=29) (actual time=0.104..0.104 rows=0 loops=1)

  Buffers: shared hit=4

  ->  Index Scan using index_statistical_info on public.statistical_info  (cost=0.00..8.27 rows=1 width=29) (actual time=0.077..0.080 rows=1 loops=1)

        Output: id, receiver_id, message_id, message_source, true, is_read, created_at, ctid

        Index Cond: ((statistical_info.message_id = 10556434) AND (statistical_info.message_source = 1::smallint))

        Filter: (statistical_info.receiver_id = 858582)

        Buffers: shared hit=3

Total runtime: 0.158 ms

先根据receiver_id进行过滤，然后扫描（message_id， message_source）上的索引。第一次更新Buffers: shared未命中，耗时7ms,后面Buffers: shared命中，只需要0.158 ms。