专场13.1-PostgreSQL之SQL优化小技巧-傅强.pdf

1、PostgreSQL之SQL优化小技巧 傅强 美创科技DBA表的扫描方式01表的连接方式02SQL改写03数据库配置04架构设计05CONTENTS目 录相关工具06表的扫描方式扫描方式简称扫描方式简称扫描方式说明扫描方式说明Seq Scan顺序扫描整个对象Parallel Seq Scan采用并行方式顺序扫描整个对象Index Scan采用离散读的方式，利用索引访问某个对象Index Only Scan仅通过索引，不访问表快速访问某个对象Bitmap Index Scan通过多个索引扫描后形成位图找到符合条件的数据Bitmap Heap Scan往往跟随bitmap index scan，使

2、用该扫描生成的位图访问对象在等值或范围查询、排序及分组查询时使用索引字段，以尽量避免表扫描postgres=#explain analyze select*from tbl_index where a=557858;QUERY PLAN -Seq Scan on tbl_index (cost=0.00.18870.00 rows=2 width=21)(actual time=0.036.671.703 rows=1 loops=1)Filter:(a=557858)Rows Removed by Filter:999999 Planning Time:0.344 ms Execution

3、Time:671.862 ms(5 rows)postgres=#explain analyze select*from tbl_index where a=557858;QUERY PLAN -Index Scan using tbl_index_a on tbl_index (cost=0.42.12.46 rows=2 width=21)(actual time=0.145.0.147 rows=1 loops=1)Index Cond:(a=557858)Planning Time:0.367 ms Execution Time:0.235 ms(4 rows)减少使用导致索引扫描失效

4、的SQL语句书写方式（函数，表达式等）postgres=#explain analyze select*from tbl_index where a:varchar=557858;QUERY PLAN -Seq Scan on tbl_index (cost=0.00.23870.00 rows=5000 width=21)(actual time=0.056.769.264 rows=1 loops=1)Filter:(a):character varying):text=557858:text)Rows Removed by Filter:999999 Planning Time:0.65

5、8 ms Execution Time:769.335 ms(5 rows)表的扫描方式索引类型不匹配表的扫描方式collate不一致数据类型不一致表中数据量少时索引字段在表中占比较高postgres=#explain analyze select*from test where id 10000;QUERY PLAN -Seq Scan on test (cost=0.00.169248.60 rows=10539 width=8)(actual time=0.053.2305.860 rows=9999 loops=1)Filter:(id 10000)Rows Removed by Fi

6、lter:9990001 Planning Time:1.076 ms Execution Time:2307.000 ms(5 rows)表的扫描方式postgres=#explain analyze select*from test where id Parallel Seq Scan on test (cost=0.00.75498.15 rows=2635 width=8)(actual time=627.179.787.198 rows=2000 loops=5)Filter:(id 10000)Rows Removed by Filter:1998000 Planning Time