PG--執行計劃之輔助算法

Sort

需要排序資料時，這個節點就會作為計劃樹的一部分被添加。可以顯式或隐式地要求排序資料

postgres=# drop table demotable;
DROP TABLE
postgres=# CREATE TABLE demotable (num numeric, id int);
CREATE TABLE
postgres=# INSERT INTO demotable SELECT random() * 1000, generate_series(1, 10000);
INSERT 0 10000
postgres=# explain select * from demotable order by num;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Sort  (cost=570.84..588.31 rows=6985 width=36)
   Sort Key: num
   ->  Seq Scan on demotable  (cost=0.00..124.85 rows=6985 width=36)
(3 rows)      

即使使用者要求最終輸出按已排序的順序，如果對應的表和排序列上有索引，則可能不會在最終計劃中添加Sort節點。

postgres=# CREATE INDEX demoidx ON demotable(num);
CREATE INDEX
postgres=# explain select * from demotable order by num;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Index Scan using demoidx on demotable  (cost=0.29..534.28 rows=10000 width=15)
(1 row)      

Merge Join需要在連接配接之前對兩個表資料進行排序。是以，可能會發現Merge Join比其他任何連接配接方法的成本都要低，即使需要額外的排序成本。是以，在本例中，Sort節點将添加到表的連接配接和掃描方法之間，以便将已排序的記錄傳遞給連接配接方法。

postgres=# create table demo1(id int, id2 int);
CREATE TABLE
postgres=# insert into demo1 values(generate_series(1,1000), generate_series(1,1000));
INSERT 0 1000
postgres=# create table demo2(id int, id2 int);
CREATE TABLE
postgres=#  create index demoidx2 on demo2(id);
CREATE INDEX
postgres=# insert into demo2 values(generate_series(1,100000), generate_series(1,100000));
INSERT 0 100000
postgres=# analyze;
ANALYZE
postgres=# explain select * from demo1, demo2 where demo1.id=demo2.id;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Merge Join  (cost=65.12..115.04 rows=1000 width=16)
   Merge Cond: (demo2.id = demo1.id)
   ->  Index Scan using demoidx2 on demo2  (cost=0.29..3050.29 rows=100000 width=8)
   ->  Sort  (cost=64.83..67.33 rows=1000 width=8)
         Sort Key: demo1.id
         ->  Seq Scan on demo1  (cost=0.00..15.00 rows=1000 width=8)
(6 rows)      

Aggregate

如果有某個聚合函數用于從多個輸入元組中計算以得到單個結果，那麼Aggregate節點就會作為規劃樹的一部分被添加。一些常用的聚合函數是COUNT, SUM, AVG (AVERAGE)， MAX (MAXIMUM)和MIN (MINIMUM)。

postgres=# explain select count(*) from demo1;
                          QUERY PLAN                           
---------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on demo1  (cost=0.00..15.00 rows=1000 width=0)
(2 rows)

postgres=# explain select sum(demo1.id) from demo1, demo2 where demo1.id=demo2.id;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=117.54..117.55 rows=1 width=8)
   ->  Merge Join  (cost=65.12..115.04 rows=1000 width=4)
         Merge Cond: (demo2.id = demo1.id)
         ->  Index Only Scan using demoidx2 on demo2  (cost=0.29..3050.29 rows=100000 width=4)
         ->  Sort  (cost=64.83..67.33 rows=1000 width=4)
               Sort Key: demo1.id
               ->  Seq Scan on demo1  (cost=0.00..15.00 rows=1000 width=4)
(7 rows)      

HashAggregate

如果聚合發生在未排序的資料集上，那麼HashAggregate可以被組級聚合使用

postgres=# explain select count(*) from demo1 group by id2;
                          QUERY PLAN                           
---------------------------------------------------------------
 HashAggregate  (cost=20.00..30.00 rows=1000 width=12)
   Group Key: id2
   ->  Seq Scan on demo1  (cost=0.00..15.00 rows=1000 width=4)
(3 rows)      

這裡的demo1表模式資料與前一節中顯示的示例相同。由于隻需要對1000行進行分組，是以建構哈希表所需的資源要小于排序的成本。查詢規劃器決定選擇HashAggregate。

GroupAggregate

GroupAggregate用于處理已經排好序的資料，是以不需要任何額外的資料結構

postgres=# explain select count(*) from demo2 group by id2;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 GroupAggregate  (cost=9747.82..11497.82 rows=100000 width=12)
   Group Key: id2
   ->  Sort  (cost=9747.82..9997.82 rows=100000 width=4)
         Sort Key: id2
         ->  Seq Scan on demo2  (cost=0.00..1443.00 rows=100000 width=4)
(5 rows)      

其他常用

limit （SELECT查詢中使用了“ limit / offset”子句）

explain select * from demo1 offset 5 limit 10;      

unique （distinct）

explain select distinct(id) from demo2 where id<100;      

lockrows（for update）

explain select * from demo1 for update;      

setop （合并結果）

explain select * from emp where age>25 intersect select * from emp where salary > 95;      

append （union）

subquery scan （子查詢）