2023年8月1日 星期二

PostgreSQL 好用函數 Format()，快速盤點不常用 Index 產生清除語法。

在資料庫應用上，一般我們都會透過 || 符號或是 concat 這類函數進行字串的串接，在內容較簡單的時候這些方法足夠使用了，但是當我們需要做複雜的處理的時候，還是得透過 Format 語法來建構較複雜的結果。


這邊舉一個例子來說明，下面是一段用來查找不常用 Index 的語法，以下範例只有找到一個，因此我們可以很輕易的寫出移除語法，但是當我們結果有數百個甚至數千個的時候，這件事就會變得不容易了。

--以下語法僅針對非 Partition 表格，且非 UK 或 PK

select
  sti.schemaname,
  sti.relname,
  sti.indexrelname,
  sti.idx_scan
from pg_stat_all_indexes sti
JOIN pg_class c ON c.oid = sti.indexrelid
JOIN pg_index i ON i.indexrelid =  sti.indexrelid
WHERE
      sti.schemaname <> 'pg_catalog'
      AND sti.schemaname !~ '^pg_toast'
      AND sti.schemaname <> 'information_schema'
      AND sti.schemaname <> 'sys'
      AND sti.schemaname <> 'pgagent' --Exclude pgagent
      AND sti.indexrelname <> 'spatial_ref_sys_pkey' --Exclude PostGIS
      AND c.relispartition = 'f'
      AND i.indisunique = 'f'
      AND i.indisprimary = 'f'
      AND sti.idx_scan < 1000
order by sti.idx_scan desc; 

 schemaname | relname | indexrelname  | idx_scan
------------+---------+---------------+----------
 public     | test    | test_col_idx  |        0
 public     | test    | test_col_idx1 |        0
(2 rows)

而使用 Format 語法可以幫助我們很輕易達到這件事情，此方法可以應用在許多地方，當我們透過系統表查出所需要的資訊後，再搭配 format 語法來串出我們需要的 SQL 語法。

得到需要的語法後，可以直接在 psql 執行 \gexec 就會把產生的語法執行下去，
或是再依需求存下來執行。
select
  --sti.schemaname,
  --sti.relname,
  --sti.indexrelname,
  --sti.idx_scan
  --, 
  format($$drop index %s.%s; $$, sti.schemaname ,sti.indexrelname)
from pg_stat_all_indexes sti
JOIN pg_class c ON c.oid = sti.indexrelid
JOIN pg_index i ON i.indexrelid =  sti.indexrelid
WHERE
      sti.schemaname <> 'pg_catalog'
      AND sti.schemaname !~ '^pg_toast'
      AND sti.schemaname <> 'information_schema'
      AND sti.schemaname <> 'sys'
      AND sti.schemaname <> 'pgagent' --Exclude pgagent
      AND sti.indexrelname <> 'spatial_ref_sys_pkey' --Exclude PostGIS
      AND c.relispartition = 'f'
      AND i.indisunique = 'f'
      AND i.indisprimary = 'f'
      AND sti.idx_scan < 1000
order by sti.idx_scan desc; 

              format
-----------------------------------
 drop index public.test_col_idx;
 drop index public.test_col_idx1;
(2 rows)


附錄：清查 Partition Table 用語法
select 
  c1.relnamespace::regnamespace,
  c1.relname,
  c2.relname,
  sum(sti.idx_scan) AS idx_scan_sum
  -- ,format('drop index %s.%s; ', c1.relnamespace::regnamespace ,c2.relname)
from pg_partitioned_table p
JOIN pg_class c1 ON p.partrelid = c1.oid
JOIN pg_index pi ON c1.oid = pi.indrelid
JOIN pg_inherits i ON i.inhparent = pi.indexrelid
JOIN pg_stat_all_indexes sti ON i.inhrelid = sti.indexrelid
JOIN pg_class c2 on c2.oid = pi.indexrelid
WHERE pi.indisunique = 'f'
      AND pi.indisprimary = 'f'
GROUP BY c1.relnamespace, c1.relname, c2.relname
HAVING sum(sti.idx_scan) < 1000
;


on