PostgreSQL (慢SQL|数据库整体变慢|性能抖动) 数据库性能分析与优化方法

explain (analyze,verbose,timing,costs,buffers) select gid,info,crt_Time from   
  (select *, row_number() over (partition by gid order by crt_time desc) as rn from tbl_log) t  
where rn=1;   
  
                                                                          QUERY PLAN                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on t  (cost=0.43..488005.99 rows=25000 width=45) (actual time=0.036..3116.007 rows=11 loops=1)  
   Output: t.gid, t.info, t.crt_time  
   Filter: (t.rn = 1)  
   Buffers: shared hit=16266 read=517194 written=8941  
   ->  WindowAgg  (cost=0.43..425505.99 rows=5000000 width=53) (actual time=0.035..3115.996 rows=11 loops=1)  
         Output: tbl_log.gid, tbl_log.info, tbl_log.crt_time, row_number() OVER (?)  
         Run Condition: (row_number() OVER (?) <= 1)  
         Buffers: shared hit=16266 read=517194 written=8941  
         ->  Index Scan using idx_tbl_log_1 on public.tbl_log  (cost=0.43..338005.99 rows=5000000 width=45) (actual time=0.026..2736.351 rows=5000000 loops=1)  
               Output: tbl_log.gid, tbl_log.crt_time, tbl_log.info  
               Buffers: shared hit=16266 read=517194 written=8941  
 Planning:  
   Buffers: shared hit=18 read=1 dirtied=2  
 Planning Time: 0.630 ms  
 Execution Time: 3116.041 ms  
(15 rows)  

with RECURSIVE tmp as (  
(select tbl_log as t from tbl_log order by gid, crt_time desc limit 1)  
union all   
select (select tbl_log from tbl_log where tbl_log.gid > (tmp.t).gid order by tbl_log.gid, tbl_log.crt_time desc limit 1) as t  
from tmp where tmp.* is not null   
)  
select (tmp.t).* from tmp   
where tmp.* is not null;  
  
  
 gid |               info               |          crt_time            
-----+----------------------------------+----------------------------  
   0 | 144ccff07b812d0ca5252ae8cbc2ad50 | 2022-08-23 14:59:59.531316  
   1 | 22fb4e6bb2daa15fcb8b00358bb4f3ad | 2022-08-23 14:59:59.531342  
   2 | 43761591e939309f1bb9e2b94f642e6d | 2022-08-23 14:59:59.531356  
   3 | 1751a3a7884685ec2c16926b4e2ad607 | 2022-08-23 14:59:59.531341  
   4 | 5df93803d19bf3a6bd19b7d017757bed | 2022-08-23 14:59:59.531348  
   5 | c11384fa2434c67992d14da837f65ac0 | 2022-08-23 14:59:59.531352  
   6 | ea33278a5f8d75c75ddbcbf7d753367f | 2022-08-23 14:59:59.531355  
   7 | c98c67d0a08c2f6dc865a291997748d5 | 2022-08-23 14:59:59.531347  
   8 | 644215ca6c3f2ad0fc1c0387a8e5c4fb | 2022-08-23 14:59:59.53133  
   9 | d0b554588b4a1d3de9fddcac630234ea | 2022-08-23 14:59:59.531354  
  10 | 903c0dda9ddfbd241043b8d75b4eaf22 | 2022-08-23 14:59:59.531351  
(11 rows)  
  
Time: 0.603 ms