PostgreSQL 效能調校技巧

使用任何 DB , 如果發現 AP 的效能變差, 能夠立即有成效的效能調校就是檢視 index 的建立與使用狀況..

• 針對非英文語系的資料內容, 要建立的 index 應該要加上 xxx_pattern_ops

Exp : docmsg → docsubject 的資料型態是 character varying(1024), 內容主要為中文字, 原本建立的 index 如下:

CREATE INDEX docmsg_docsubject_idx
  ON docmsg
  USING btree (docsubject);

發現使用以下的語法透過 Explain 是不會用到 docmsg_docsubject_idx 這個 index

SELECT docsubject
  FROM docmsg
  WHERE docsubject LIKE '%測試%';

當 index 加上 pattern_ops 之後, 改成如下語法

CREATE INDEX docmsg_docsubject_idx
  ON docmsg
  USING btree (docsubject varchar_pattern_ops);

在執行一次剛剛的 Explain SQL 指令, 就可以發現可以正確使用到這個 index

• 參考資料 : PostgreSQL 手冊

• 針對 SQL 內條件部份, 如果比對的資料型態不同, 應該要強制指定

Exp : xmlboxdocnum2 → docdate 的資料型態是 character(10), 有建立 index 如下:

CREATE INDEX xmlboxdocnum2_docdate_idx
  ON xmlboxdocnum2
  USING btree (docdate);

發現使用以下的語法透過 Explain 是不會用到 xmlboxdocnum2_docdate_idx 這個 index

SELECT devorgid,count(docdate)
  FROM xmlboxdocnum2
  WHERE docdate >= current_date-7
  GROUP BY devorgid;

當加上 ::character varying 之後, 改成如下語法

SELECT devorgid,count(docdate)
  FROM xmlboxdocnum2
  WHERE docdate >= (current_date-7)::character varying
  GROUP BY devorgid;

在執行一次剛剛的 Explain SQL 指令, 就可以發現可以正確使用到這個 index

• 如果要建立 index 的欄位內容出現 NULL , 是不會被加入這個索引資料內容, 所以當查詢條件出現 xxx is NULL 就不會使用到該 index