天天看點
傳回

【重新發現PostgreSQL之美】 - 6 index連結清單跳跳糖 (CTE recursive 遞歸的詳細用例)

背景

CTE 遞歸文法是PG 8.4引入的功能, 至今已經10多年, 非常文檔.

CTE 遞歸可以解決很多問題: 時序場景取所有傳感器最新的value, 圖式資料的搜尋(一度人脈,N度人脈,最近的路徑關系), 樹狀資料的累加分析, 知識圖譜, 去稀疏資料的唯一值等.

使用CTE遞歸比通用的方法通常有數百倍的性能提升.

https://github.com/digoal/blog/blob/master/202105/20210529_01.md#%E7%94%A8%E4%BE%8B 用例

假設傳感器有1萬個, 每個傳感器每秒上傳一條記錄.

取出今天處于活躍狀态(有資料)的傳感器的最後一個值.

1、建立測試表 

create unlogged table tbl_sensor_log (  
  id serial8 ,     
  sid int, -- 傳感器ID (例如 網約車、警車、巡邏車、共享單車、物聯網傳感器等裝置)  
  val jsonb, -- 傳感器上傳的資料  
  crt_time timestamp  -- 上傳時間  
)  
partition by range (crt_time)  
;

2、建立分區 

do language plpgsql $$  
declare  
begin  
  for i in 0..365 loop  
    execute format($_$  
        create unlogged table tbl_sensor_log_%s PARTITION of tbl_sensor_log   
        for values from (%L) to (%L)  
    $_$, to_char(current_date+i, 'yyyymmdd'), current_date+i, current_date+i+1);  
  end loop;  
end;  
$$;

3、建立索引 

insert into tbl_sensor_log (sid, val, crt_time)  
  select random()*10000 , row_to_json(row(random(),random(),clock_timestamp())), current_date+(round(random()::numeric*72::numeric,2)||' hour')::interval  
from generate_series(1,50000000);

4、寫入5000萬條記錄, 均勻分布在最近3天的分區内​ 

insert into tbl_sensor_log (sid, val, crt_time)  
  select random()*10000 , row_to_json(row(random(),random(),clock_timestamp())), current_date+(round(random()::numeric*72::numeric,2)||' hour')::interval  
from generate_series(1,50000000);

方法1: 使用傳統的視窗查詢

select id,sid,val,crt_time from   
(  
select *, row_number() over w as RN  
  from tbl_sensor_log   
  where crt_time >= current_date and crt_time < current_date+1   
  window w as (partition by sid order by crt_time desc)  
) t  
where rn=1;  
  
  
  
  
  
                                                                                    QUERY PLAN                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on t  (cost=5057407.30..5598899.49 rows=83306 width=119) (actual time=40763.507..52716.622 rows=10001 loops=1)  
   Filter: (t.rn = 1)  
   Rows Removed by Filter: 16653784  
   ->  WindowAgg  (cost=5057407.30..5390633.26 rows=16661298 width=127) (actual time=40763.503..51533.043 rows=16663785 loops=1)  
         ->  Sort  (cost=5057407.30..5099060.55 rows=16661298 width=119) (actual time=40763.483..44945.556 rows=16663785 loops=1)  
               Sort Key: tbl_sensor_log.sid, tbl_sensor_log.crt_time DESC  
               Sort Method: external merge  Disk: 2177080kB  
               ->  Append  (cost=0.00..1257703.57 rows=16661298 width=119) (actual time=0.065..5597.541 rows=16663785 loops=1)  
                     Subplans Removed: 365  
                     ->  Seq Scan on tbl_sensor_log_20210529 tbl_sensor_log_1  (cost=0.00..683635.38 rows=16660933 width=119) (actual time=0.064..4066.655 rows=16663785 loops=1)  
                           Filter: ((crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
 Planning Time: 219.559 ms  
 Execution Time: 53133.463 ms  
(13 rows)  
  
  
                                                                                                          QUERY PLAN                                                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on t  (cost=65.16..10962058.77 rows=83306 width=119) (actual time=0.041..104082.386 rows=10001 loops=1)  
   Filter: (t.rn = 1)  
   Rows Removed by Filter: 16653784  
   ->  WindowAgg  (cost=65.16..10753792.55 rows=16661298 width=127) (actual time=0.040..102532.935 rows=16663785 loops=1)  
         ->  Merge Append  (cost=65.16..10462219.83 rows=16661298 width=119) (actual time=0.029..92266.387 rows=16663785 loops=1)  
               Sort Key: tbl_sensor_log.sid, tbl_sensor_log.crt_time DESC  
               Subplans Removed: 365  
               ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 tbl_sensor_log_1  (cost=0.44..9177871.39 rows=16660933 width=119) (actual time=0.029..89908.207 rows=16663785 loops=1)  
                     Index Cond: ((crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
 Planning Time: 39.511 ms  
 Execution Time: 104088.128 ms  
(11 rows)

方法2: 使用索引連結清單跳跳糖

遞歸, 每次掃描定位到1個目标SID, 然後跳到第二個SID, 而不是通過索引連結清單順序掃描.

連結清單順序掃描的缺點: 整張索引的時間範圍内的所有葉子結點的每個page都要掃描到, 性能爛到家.

with recursive tmp as (  
(select t from tbl_sensor_log as t where crt_time >= current_date and crt_time < current_date+1 order by sid, crt_time desc limit 1)  
union all   
select (select tbl_sensor_log from tbl_sensor_log where sid>(tmp.t).sid   
        and crt_time >= current_date and crt_time < current_date+1 order by sid, crt_time desc limit 1) as t  
from tmp where tmp.* is not null  
)  
select (tmp.t).* from tmp   
where tmp.* is not null;  
  
  
  
                                                                                QUERY PLAN                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 CTE Scan on tmp  (cost=6650.50..6652.52 rows=100 width=52)  
   Filter: (tmp.* IS NOT NULL)  
   CTE tmp  
     ->  Recursive Union  (cost=65.16..6650.50 rows=101 width=32)  
           ->  Subquery Scan on "*SELECT* 1"  (cost=65.16..65.80 rows=1 width=32)  
                 ->  Limit  (cost=65.16..65.79 rows=1 width=44)  
                       ->  Merge Append  (cost=65.16..10462219.83 rows=16661298 width=44)  
                             Sort Key: t.sid, t.crt_time DESC  
                             Subplans Removed: 365  
                             ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 t_1  (cost=0.44..9177871.39 rows=16660933 width=44)  
                                   Index Cond: ((crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
           ->  WorkTable Scan on tmp tmp_1  (cost=0.00..658.27 rows=10 width=32)  
                 Filter: (tmp_1.* IS NOT NULL)  
(13 rows)  
  
  
  
                                                                                                              QUERY PLAN                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 CTE Scan on tmp  (cost=6650.50..6652.52 rows=100 width=52) (actual time=0.036..124.680 rows=10001 loops=1)  
   Filter: (tmp.* IS NOT NULL)  
   Rows Removed by Filter: 1  
   CTE tmp  
     ->  Recursive Union  (cost=65.16..6650.50 rows=101 width=32) (actual time=0.032..119.486 rows=10002 loops=1)  
           ->  Subquery Scan on "*SELECT* 1"  (cost=65.16..65.80 rows=1 width=32) (actual time=0.031..0.033 rows=1 loops=1)  
                 ->  Limit  (cost=65.16..65.79 rows=1 width=44) (actual time=0.031..0.032 rows=1 loops=1)  
                       ->  Merge Append  (cost=65.16..10462219.83 rows=16661298 width=44) (actual time=0.030..0.030 rows=1 loops=1)  
                             Sort Key: t.sid, t.crt_time DESC  
                             Subplans Removed: 365  
                             ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 t_1  (cost=0.44..9177871.39 rows=16660933 width=44) (actual time=0.029..0.030 rows=1 loops=1)  
                                   Index Cond: ((crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
           ->  WorkTable Scan on tmp tmp_1  (cost=0.00..658.27 rows=10 width=32) (actual time=0.011..0.011 rows=1 loops=10002)  
                 Filter: (tmp_1.* IS NOT NULL)  
                 Rows Removed by Filter: 0  
                 SubPlan 1  
                   ->  Limit  (cost=65.16..65.81 rows=1 width=44) (actual time=0.011..0.011 rows=1 loops=10001)  
                         ->  Merge Append  (cost=65.16..3572009.02 rows=5554009 width=44) (actual time=0.011..0.011 rows=1 loops=10001)  
                               Sort Key: tbl_sensor_log.sid, tbl_sensor_log.crt_time DESC  
                               Subplans Removed: 365  
                               ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 tbl_sensor_log_1  (cost=0.44..3115515.85 rows=5553644 width=44) (actual time=0.010..0.010 rows=1 loops=10001)  
                                     Index Cond: ((sid > (tmp_1.t).sid) AND (crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
 Planning Time: 69.016 ms  
 Execution Time: 125.866 ms  
(24 rows)

方法3: 使用subquery

但是, 需要維護一張SID表, 實際業務邏輯可能比這複雜, SID表可能沒有這麼好維護.

而且還有1個問題: 今天沒有活躍的SID也會被查出來. 如果選擇過濾今天不活躍的記錄, 需要多次評估, 性能就會下降.

create table tbl_sensor (sid int primary key);  
insert into tbl_sensor select generate_series(0,10010);  
  
  
  
select (select tbl_sensor_log from tbl_sensor_log where sid=t.sid and crt_time >= current_date and crt_time < current_date+1 order by crt_time desc limit 1) as val  
from tbl_sensor as t;  
  
  
                                                                             QUERY PLAN                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Index Only Scan using tbl_sensor_pkey on tbl_sensor t  (cost=0.29..509812.03 rows=10011 width=32)  
   SubPlan 1  
     ->  Limit  (cost=49.58..50.91 rows=1 width=40)  
           ->  Append  (cost=49.58..2751.07 rows=2036 width=40)  
                 Subplans Removed: 365  
                 ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 tbl_sensor_log_1  (cost=0.44..1880.54 rows=1671 width=40)  
                       Index Cond: ((sid = t.sid) AND (crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
(7 rows)  
  
  
  
                                                                                                    QUERY PLAN                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Index Only Scan using tbl_sensor_pkey on tbl_sensor t  (cost=0.29..509812.03 rows=10011 width=32) (actual time=0.039..116.315 rows=10011 loops=1)  
   Heap Fetches: 0  
   SubPlan 1  
     ->  Limit  (cost=49.58..50.91 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=10011)  
           ->  Append  (cost=49.58..2751.07 rows=2036 width=40) (actual time=0.011..0.011 rows=1 loops=10011)  
                 Subplans Removed: 365  
                 ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 tbl_sensor_log_1  (cost=0.44..1880.54 rows=1671 width=40) (actual time=0.010..0.010 rows=1 loops=10011)  
                       Index Cond: ((sid = t.sid) AND (crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
 Planning Time: 40.798 ms  
 Execution Time: 117.059 ms  
(10 rows)

過濾不活躍的SID将增加計算量, 性能有下降.

select * from   
(  
select (select tbl_sensor_log from tbl_sensor_log where sid=t.sid and crt_time >= current_date and crt_time < current_date+1 order by crt_time desc limit 1) as val  
from tbl_sensor as t  
) t1  
where t1.val is not null;   
  
  
                                                                                                    QUERY PLAN                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Index Only Scan using tbl_sensor_pkey on tbl_sensor t  (cost=0.29..1016895.27 rows=9961 width=32) (actual time=0.044..173.412 rows=10001 loops=1)  
   Filter: ((SubPlan 2) IS NOT NULL)  
   Rows Removed by Filter: 10  
   Heap Fetches: 0  
   SubPlan 1  
     ->  Limit  (cost=49.58..50.91 rows=1 width=40) (actual time=0.007..0.007 rows=1 loops=10001)  
           ->  Append  (cost=49.58..2751.07 rows=2036 width=40) (actual time=0.007..0.007 rows=1 loops=10001)  
                 Subplans Removed: 365  
                 ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 tbl_sensor_log_1  (cost=0.44..1880.54 rows=1671 width=40) (actual time=0.006..0.006 rows=1 loops=10001)  
                       Index Cond: ((sid = t.sid) AND (crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
   SubPlan 2  
     ->  Limit  (cost=49.58..50.91 rows=1 width=40) (actual time=0.010..0.010 rows=1 loops=10011)  
           ->  Append  (cost=49.58..2751.07 rows=2036 width=40) (actual time=0.010..0.010 rows=1 loops=10011)  
                 Subplans Removed: 365  
                 ->  Index Scan using tbl_sensor_log_20210529_sid_crt_time_idx on tbl_sensor_log_20210529 tbl_sensor_log_3  (cost=0.44..1880.54 rows=1671 width=40) (actual time=0.009..0.009 rows=1 loops=10011)  
                       Index Cond: ((sid = t.sid) AND (crt_time >= CURRENT_DATE) AND (crt_time < (CURRENT_DATE + 1)))  
 Planning Time: 68.114 ms  
 Execution Time: 174.239 ms  
(18 rows)

更多CTE的應用場景

https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20210320210320_02md---postgresql-14-preview---recovery-%E6%80%A7%E8%83%BD%E5%A2%9E%E5%BC%BA---recovery_init_sync_methodsyncfs---%E8%A7%A3%E5%86%B3%E8%A1%A8%E5%BE%88%E5%A4%9A%E6%97%B6-crash-recovery-%E9%80%92%E5%BD%92open%E6%89%80%E6%9C%89file%E7%9A%84%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98---%E9%9C%80linux%E6%96%B0%E5%86%85%E6%A0%B8%E6%94%AF%E6%8C%81 202103/20210320_02.md  《PostgreSQL 14 preview - recovery 性能增強 - recovery_init_sync_method=syncfs - 解決表很多時, crash recovery 遞歸open所有file的性能問題 - 需Linux新核心支援》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20210220210201_03md---postgresql-14-preview---sql%E6%A0%87%E5%87%86%E5%A2%9E%E5%BC%BA-%E9%80%92%E5%BD%92cte%E5%9B%BE%E5%BC%8F%E6%90%9C%E7%B4%A2%E5%A2%9E%E5%8A%A0%E5%B9%BF%E5%BA%A6%E4%BC%98%E5%85%88%E6%B7%B1%E5%BA%A6%E4%BC%98%E5%85%88%E8%AF%AD%E6%B3%95-%E5%BE%AA%E7%8E%AF%E8%AF%AD%E6%B3%95---breadth--or-depth-first-search-orders-and-detect-cycles 202102/20210201_03.md  《PostgreSQL 14 preview - SQL标準增強, 遞歸(CTE)圖式搜尋增加廣度優先、深度優先文法, 循環文法 - breadth- or depth-first search orders and detect cycles》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20201120201125_01md---postgresql-%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2%E5%9C%A8%E5%88%86%E7%BB%84%E5%90%88%E5%B9%B6%E4%B8%AD%E7%9A%84%E7%94%A8%E6%B3%95 202011/20201125_01.md  《PostgreSQL 遞歸查詢在分組合并中的用法》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20200620200615_01md---%E9%80%92%E5%BD%92%E6%8E%92%E5%BA%8F%E5%AD%97%E6%AE%B5%E5%8A%A0%E6%9D%83-skip-scan-%E8%A7%A3%E5%86%B3-%E7%AA%97%E5%8F%A3%E6%9F%A5%E8%AF%A2%E5%A4%9A%E5%88%97%E5%88%86%E7%BB%84%E5%8E%BB%E9%87%8D%E7%9A%84%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98 202006/20200615_01.md  《遞歸+排序字段權重 skip scan 解決 視窗查詢多列分組去重的性能問題》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20200520200515_01md---postgresql-%E6%8E%92%E5%BA%8F%E5%8E%BB%E9%87%8Dlimit%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96---%E9%80%92%E5%BD%92-vs-group%E5%88%86%E7%BB%84-loop%E9%99%8D%E5%88%B0%E6%9E%81%E9%99%90-block-scan%E9%99%8D%E5%88%B0%E6%9E%81%E9%99%90 202005/20200515_01.md  《PostgreSQL 排序去重limit查詢優化 - 遞歸 vs group分組 (loop降到極限, block scan降到極限)》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20200320200329_01md---postgresql-%E5%AE%B6%E6%97%8F%E5%9B%BE%E8%B0%B1%E7%A4%BE%E4%BA%A4%E5%9B%BE%E8%B0%B1%E6%A0%91%E7%8A%B6%E5%85%B3%E7%B3%BB%E8%97%A4%E7%8A%B6%E5%88%86%E4%BD%A3%E6%BA%AF%E6%BA%90%E7%AD%89%E5%9C%BA%E6%99%AF%E5%AE%9E%E8%B7%B5---%E9%80%92%E5%BD%92with-recursive-query-%E6%9C%89%E5%90%91%E6%97%A0%E7%8E%AF--%E6%9C%89%E5%90%91%E6%9C%89%E7%8E%AF 202003/20200329_01.md  《PostgreSQL 家族圖譜、社交圖譜、樹狀關系、藤狀分傭、溯源、等場景實踐 - 遞歸,with recursive query (有向無環 , 有向有環)》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20200220200228_01md---%E7%B4%AF%E5%8A%A0%E9%93%BE%E6%9D%A1%E4%BB%B6%E8%BF%87%E6%BB%A4---%E9%80%92%E5%BD%92%E7%AA%97%E5%8F%A3udf%E6%B8%B8%E6%A0%87%E6%A8%A1%E6%8B%9F%E9%80%92%E5%BD%92scan-%E5%89%AA%E5%88%87 202002/20200228_01.md  《累加鍊條件過濾 - 遞歸、視窗、UDF、遊标、模拟遞歸、scan 剪切》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20190320190318_04md---postgresql-%E5%B9%B6%E8%A1%8C%E8%AE%A1%E7%AE%97%E8%A7%A3%E8%AF%B4-%E4%B9%8B29---parallel-%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2-%E6%A0%91%E7%8A%B6%E6%9F%A5%E8%AF%A2-%E5%BC%82%E6%9E%84%E6%9F%A5%E8%AF%A2-cte-recursive-cte-connect-by 201903/20190318_04.md  《PostgreSQL 并行計算解說 之29 - parallel 遞歸查詢, 樹狀查詢, 異構查詢, CTE, recursive CTE, connect by》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20180820180808_02md---postgresql-%E9%80%92%E5%BD%92%E5%BA%94%E7%94%A8%E5%AE%9E%E8%B7%B5---%E9%9D%9E%E4%BC%A0%E9%94%80%E7%9A%84%E9%AB%98%E5%B9%B6%E5%8F%91%E5%AE%9E%E6%97%B6%E8%97%A4%E6%A0%91%E7%8A%B6%E4%BD%A3%E9%87%91%E5%88%86%E9%85%8D%E4%BD%93%E7%B3%BB 201808/20180808_02.md  《PostgreSQL 遞歸應用實踐 - 非“傳銷”的高并發實時藤、樹狀傭金配置設定體系》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20180420180406_01md---postgresql-%E9%80%92%E5%BD%92%E5%A6%99%E7%94%A8%E6%A1%88%E4%BE%8B---%E5%88%86%E7%BB%84%E6%95%B0%E6%8D%AE%E5%8E%BB%E9%87%8D%E4%B8%8E%E6%89%93%E6%95%A3 201804/20180406_01.md  《PostgreSQL 遞歸妙用案例 - 分組資料去重與打散》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20180320180323_03md---postgresql-oracle-%E5%85%BC%E5%AE%B9%E6%80%A7%E4%B9%8B---index-skip-scan-%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2%E5%8F%98%E6%80%81%E4%BC%98%E5%8C%96-%E9%9D%9E%E9%A9%B1%E5%8A%A8%E5%88%97%E7%B4%A2%E5%BC%95%E6%89%AB%E6%8F%8F%E4%BC%98%E5%8C%96 201803/20180323_03.md  《PostgreSQL Oracle 相容性之 - INDEX SKIP SCAN (遞歸查詢變态優化) 非驅動列索引掃描優化》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20170520170519_01md---postgrsql-%E9%80%92%E5%BD%92sql%E7%9A%84%E5%87%A0%E4%B8%AA%E5%BA%94%E7%94%A8---%E6%9E%81%E5%AE%A2%E4%B8%8E%E6%AD%A3%E5%B8%B8%E4%BA%BA%E7%9A%84%E6%80%9D%E7%BB%B4 201705/20170519_01.md  《PostgrSQL 遞歸SQL的幾個應用 - 極客與正常人的思維》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20170320170324_01md---postgresql-%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2case---%E6%A0%91%E5%9E%8B%E8%B7%AF%E5%BE%84%E5%88%86%E7%BB%84%E8%BE%93%E5%87%BA 201703/20170324_01.md  《PostgreSQL 遞歸查詢CASE - 樹型路徑分組輸出》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20161220161201_01md---%E7%94%A8postgresql%E6%89%BE%E5%9B%9E618%E7%A7%92%E9%80%9D%E5%8E%BB%E7%9A%84%E9%9D%92%E6%98%A5---%E9%80%92%E5%BD%92%E6%94%B6%E6%95%9B%E4%BC%98%E5%8C%96 201612/20161201_01.md  《用PostgreSQL找回618秒逝去的青春 - 遞歸收斂優化》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20161120161128_02md---distinct-xx%E5%92%8Ccountdistinct-xx%E7%9A%84%E5%8F%98%E6%80%81%E9%80%92%E5%BD%92%E4%BC%98%E5%8C%96%E6%96%B9%E6%B3%95---%E7%B4%A2%E5%BC%95%E6%94%B6%E6%95%9Bskip-scan%E6%89%AB%E6%8F%8F 201611/20161128_02.md  《distinct xx和count(distinct xx)的變态遞歸優化方法 - 索引收斂(skip scan)掃描》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20161120161128_01md---%E6%97%B6%E5%BA%8F%E6%95%B0%E6%8D%AE%E5%90%88%E5%B9%B6%E5%9C%BA%E6%99%AF%E5%8A%A0%E9%80%9F%E5%88%86%E6%9E%90%E5%92%8C%E5%AE%9E%E7%8E%B0---%E5%A4%8D%E5%90%88%E7%B4%A2%E5%BC%95%E7%AA%97%E5%8F%A3%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2%E5%8A%A0%E9%80%9F%E5%8F%98%E6%80%81%E9%80%92%E5%BD%92%E5%8A%A0%E9%80%9F 201611/20161128_01.md  《時序資料合并場景加速分析和實作 - 複合索引，視窗分組查詢加速，變态遞歸加速》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20160820160815_04md---postgresql%E9%9B%95%E8%99%AB%E5%B0%8F%E6%8A%80cte-%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2%E5%88%86%E7%BB%84top%E6%80%A7%E8%83%BD%E6%8F%90%E5%8D%8744%E5%80%8D 201608/20160815_04.md  《PostgreSQL雕蟲小技cte 遞歸查詢，分組TOP性能提升44倍》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20160720160725_01md---postgresql-%E4%BD%BF%E7%94%A8%E9%80%92%E5%BD%92sql-%E6%89%BE%E5%87%BA%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AF%B9%E8%B1%A1%E4%B9%8B%E9%97%B4%E7%9A%84%E4%BE%9D%E8%B5%96%E5%85%B3%E7%B3%BB---%E4%BE%8B%E5%A6%82%E8%A7%86%E5%9B%BE%E4%BE%9D%E8%B5%96 201607/20160725_01.md  《PostgreSQL 使用遞歸SQL 找出資料庫對象之間的依賴關系 - 例如視圖依賴》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20160720160723_01md---postgresql-%E9%80%92%E5%BD%92%E6%AD%BB%E5%BE%AA%E7%8E%AF%E6%A1%88%E4%BE%8B%E5%8F%8A%E8%A7%A3%E6%B3%95 201607/20160723_01.md  《PostgreSQL 遞歸死循環案例及解法》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20160420160405_01md---postgresql-%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2%E4%B8%80%E4%BE%8B---%E8%B5%84%E9%87%91%E7%B4%AF%E5%8A%A0%E9%93%BE 201604/20160405_01.md  《PostgreSQL 遞歸查詢一例 - 資金累加鍊》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20151220151221_02md---postgresql-oracle-%E5%85%BC%E5%AE%B9%E6%80%A7%E4%B9%8B---with-%E9%80%92%E5%BD%92--connect-by- 201512/20151221_02.md  《PostgreSQL Oracle 相容性之 - WITH 遞歸 ( connect by )》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20121020121009_01md---%E9%80%92%E5%BD%92%E4%BC%98%E5%8C%96case---group-by--distinct-tuning-case--use-with-recursive-and-min-function 201210/20121009_01.md  《遞歸優化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20120920120914_01md---%E9%80%92%E5%BD%92%E4%BC%98%E5%8C%96case---performance-tuning-case-use-cursortriggerrecursive-replace-group-by-and-order-by-reduce-needed-blockes-scan 201209/20120914_01.md  《遞歸優化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》
https://github.com/digoal/blog/blob/master/202105/20210529_01.md#20110520110527_01md---postgresql-%E6%A0%91%E7%8A%B6%E6%95%B0%E6%8D%AE%E5%AD%98%E5%82%A8%E4%B8%8E%E6%9F%A5%E8%AF%A2%E9%9D%9E%E9%80%92%E5%BD%92---use-ltree-extension-deal-tree-like-data-type 201105/20110527_01.md  《PostgreSQL 樹狀資料存儲與查詢(非遞歸) - Use ltree extension deal tree-like data type》
傳感器 sql 并行計算 Oracle 關系型資料庫 測試技術 Linux postgresql 知識圖譜 索引 ip位址abc.com zebra ecs nginx通過rewrite SQL語句oracle分頁 cocos2dx遊戲開發通路函數 Oracle sql優化
上一篇: ECS初體驗
下一篇: 高校學生參加飛天加速計劃

繼續閱讀