PG优化实战系列-200+s到毫秒级记录？

背景　　

　　前段时间同事在优化一个SQL，优化效果从最开始的200+s通过SQL改写优化到20s，但是依旧不能满足现场需求，于是发给我们瞅瞅。

问题解决

表情况

tab1是一张大表，一年数据量亿级别，已经按年分区存在索引 idx1（time1，colx），id唯一

原始SQL：

select count(*) from(
select id from tab1 where time1 between 1511745973 and 1606440373 and col1 = 'xx1'
union
select id from tab1 where time1 between 1511745973 and 1606440373 and col2 = 'xx2'
union
select id from tab1 where time1 between 1511745973 and 1606440373 and col3 = 'xx3') tmp;

以上SQL是取3年的数据，可以看到数据量非常大，此时已经失去分区表的作用了，就算走idx1索引效果可能会更差。

优化思路

考虑colx的过滤性，在time1过滤性差的情况下，应该考虑执行计划优先走colx的索引然后再对时间进行过滤。

create index idx_tab1_col1_time1 on tab1(col1,time1);
create index idx_tab1_col2_time1 on tab1(col2,time1);
create index idx_tab1_col3_time1 on tab1(col3,time1);

SQL改写：id字段没有重复值，可以去掉没必要的union

select count(*) from tab1 where (col1 = 'xx1' or col2 = 'xx2' or col3 = 'xx3') and time1 between 1511745973 and 1606440373;

重新执行耗时变成毫秒级别（具体多少忘了。。）

问题总结：

1.分区适合查询时只对部分分区字段进行过滤的查询，对于比如人员信息表这些将失去意义，总而言之就是分区就是为了减少对数据的扫描，适合对数据周期性淘汰。
2.多列索引的顺序直接决定所建索引是否合适，建议过滤性好的做前导列，多列索引如（id1，id2，id3）能用于（id1），（id1，id2），（id1，id2，id3）的查询
3.PG支持OR条件用组合索引（bitmapscan or）
4.尽量避免排序（改写union，distinct等）