postgresql 索引使用

系统管理员 2022-07-14 03:26 181阅读 0赞

今天给表加索引，遇到一个情况，表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。