PostgreSQL实战-006-并行查询

PostgreSQL 9.6 版本前还不支持并行查询， SQL 无法利用多核CPU 提升性能， 
PostgreSQL 9.6 版本开始支持并行查询，只是9.6 版本的并行查询所支持的范围非常有限【只在顺序扫描、多表关联、聚合查询中支持并行】
PostgreSQL 9.6  10 版本增强了并行查询功能，例如增加了并行索引扫描、并行index-only 扫描、并行bitmap heap 扫描等

本章介绍的PostgreSQL 10的并行查询功能

【6.1 并行查询相关的参数设置】
    1. max_worker_processes(integer)：设置系统支持的最大后台进程数，默认值为8 ，如果有备库，备库上此参数必须大于或等于主库上的此参数配置值，重启生效。
        ~]# grep max_worker_processes /opt/pg/data/9.5/postgresql.conf
        #max_worker_processes = 8
        
    2. max_parallel_workers (integer)：设置系统支持的并行查询进程数，默认值为8
        max_parallel_workers的值必须小于等于max_worker_processes 的值
        建议同时调整max_parallel_workers_per_gather参数值。
    3. max_parallel_workers_per_gather (integer):设置允许启用的并行进程的进程数，默认值为2 ，设置成0 表示禁用并行查询
        受max_worker_processes 参数和max_parallel_workers 参数限制
        
        这三个参数的配置值大小关系通常如下所示：
        max_worker_processes > max_parallel_workers > max_parallel_workers_per_gather
        
        并行查询进程数预估值由 max_parallel_workers_per_gather 控制，并行进程数预估值是指优化器解析SQL 时执行计划预计会启用的并行进程数，
        而实际执行查询时的并行进程数受参数max_worker_processes 、max_worker_processes的限制，即SQL 实际获得的并行进程数不会超过这两个参数设直的值
        
    4. parallel_setup_cost(floating point):设置优化器启动并行进程的成本，默认为1000 。
    5. parallel_tuple_cost(floating point):设置优化器通过并行进程处理一行数据的成本，默认为0.1 。
    
    6. min_parallel_table_scan_size(integer):设置开启并行的条件之一， 表占用空间小于此值将不会开启并行，并行顺序扫描场景下扫描的数据大小通常等于表大小， 默认值为8MB 。
    7. min_parallel_index_scan_size(integer)设置开启并行的条件之一，实际上并行索引扫描不会扫描索引所有数据块，只是扫描索引相关数据块，默认值为512kb 。
    8. force_parallel_mode (enum)强制开启并行， 一般作为测试目的， OLTP 生产环境开启需慎重，一般不建议开启。

    【实例】
    max_worker_processes = 16
    max_parallel_workers = 8
    max_parallel_workers_per_gather = 4
    parallel_setup_cost =1000.0
    parallel_tuple_cost = 0.1
    min_parallel_table_scan_size = 8M
    min_parallel_index_scan_size = 512KB
    force_parallel_mode = off
    
【6.2 并行扫描】
    并行顺序扫描、并行索引扫描、并行index-only扫描、并行bitmap heap 扫描
    
    【并行顺序扫描】
        顺序扫描（ sequential scan ），顺序扫描通常也称之为全表扫描，
            全表扫描会扫描整张表数据，当表很大时，全表扫描会占用大量CPU 、内存、IO 资源，对数据库性能有较大影响，在OLTP 事务型数据库系统中应当尽量避免。
            Seq Scan on table即在进行顺序扫描
            
            PostgreSQL 9.6 版本开始支持并行处理， 并行顺序扫描会产生多个子进程，并利用多个逻辑CPU 并行全表扫描
            
            查看执行计划
            mydb=>EXPLAIN ANALYZE SELECT * FROM test_ bigl WHERE name='l_test';
                Workers Planned 表示执行计划预估的并行进程数，
                Worker Launched 表示查询实际获得的并行进程数，
                Parallel Seq Scan on test_ big 1 表示进行了并行顺序扫描
            
            关闭并行查询
            mydb=> SET max parallel workers_per_gather =0 ;
    【并行索引扫描】
        Index Scan using 表示执行计划预计进行索引扫描， 索引扫描也支持并行，称为并行索引扫描（ Parallel index scan ）
        
        创建索引：
            mydb=> CREATE INDEX indx_test_big1_id ON test_big1 USING btree (id);
            CREATE INDEX
        
        目前PostgreSQL 10 暂不支持非btree 索引类型的并行索引扫描。
        
    【并行index-only 扫描】
        index-only 扫描是指只需扫描索引， 也就是说SQL 仅根据索引就能获得所需检索的数据， 而不需要通过索引回表查询数据。
        index-only 扫描支持并行，称为并行index-only 扫描
        
        【开启并行index-only扫描】
        mydb=> SET max_parallel_workers_per_gather TO default;
        SET
        mydb=> EXPLAIN SELECT count(*) FROM test_bigl WHERE id<1000000;
            ID 字段上建立了索引， 统计记录数不需要再回表查询其他信息， 因此进行了index-only 扫描
            
    【并行bitmap heap 扫描】
        Bitmap Index 扫描
        Bitmap Heap 扫描：
        
        当SQL的where 条件中出现or 时很有可能出现Bitmap Index 扫描，
        mydb=> EXPLAIN SELECT * FROM test_bigl WHERE id=l OR id=2 ;
            出现两次Bitmap index Scan on idx_test_bigl_id,然后将结果结合起来
            
            bitmap index scan 就相当于  index scan。只是它们需要组合起结果来，所以被称为  Bitmap Index Scan。
            Bitmap Index Scan 的结果组合起来，就是 Bitmap Heap Scan（可能涉及排序等）。
            
        mydb=> SET max_parallel_workers_per_gather =0;
        SET
        mydb=> EXPLAIN ANALYZE SELECT count (*} FROM test_bigl WHERE id <1000000 OR id > 49000000;
            进行了并行Bitmap Heap 扫描
【6.3 并行聚合】
    聚合操作是指使用min()、max()、count()、sum() 等聚合函数的SQL
    
    mydb=> SET max_parallel_workers_per_gather =4;   #建议这个数不要大于postgrep所在服务器的CPU核数
        SET
    mydb=> EXPLAIN ANALYZE SELECT count(*) FROM test_bigl;
    
【6.4 多表关联】
    多表关联也能用到并行扫描，例如nested loop 、merge join 、hash join
    多表关联场景能够使用并行并不是指多表关联本身使用并行，而是指多表关联涉及的表数据检索时能够使用并行处理
    
    【Nested loop 多表关联】
        多表关联Nested loop 实际上是一个嵌套循环， 伪代码如下所示：
        for (i = O; i < length(outer); i++)
            for (]= 0 ; ]< length(inner) ; j++)
                if (outer(i] == inner[j])
                    output(outer[i], inner[j]) ;
                    
                    
        CREATE TABLE test_small(id int4 , name character varying(32));
        INSERT INTO test small(id, name) SELECT n , n||'_small' FROM generate_series(l, 8000000) n ;
        创建索引并做表分析，如下所示：
        mydb=> CREATE INDEX idx_test_small_id ON test_small USING btree (id);
            CREATE INDEX
            
        mydb=> ANALYZE test_small;
        ANALYZE 命令用于收集表上的统计信息，使优化器能够获得更准确的执行计划
        
        mydb=> EXPLAIN ANALYZE SELECT test_small.name FROM test_big1 , test_small
                WHERE test_bigl.id = test_small.id
                AND test_small.id < 10000;
                
    【Merge join 多表关联】 
        Merge join 多表关联首先将两个表进行排序，之后进行关联宇段匹配
        
        mydb=> EXPLAIN ANALYZE SELECT test_small.name FROM test_bigl , test_small
                WHERE test big1.id = test_small.id
                AND test_small.id < 200000 ;
                
            为啥同样的查询，执行出来一个是Nested loop，一个是Merge join？？
            
    【Hash join 多表关联】
        PostgreSQL 多表关联也支持Hash join ， 当关联宇段没有索引情况下两表关联通常会进行Hash join
        
        mydb=> DROP INDEX idx_test_bigl_id;
        DROP INDEX
        mydb=> DROP INDEX idx_test_smal1_id;
        DROP INDEX
        
        mydb=> SET max_parallel_workers_per_gather =4;
        SET
        
        mydb=> EXPLAIN SELECT test_small.name FROM test_bigl JOIN test_small ON (test_bigl.id = test_small.id)
        AND test small.id < 100;