差異處

這裏顯示兩個版本的差異處。

--- tech:psql_tips [2007/08/29 17:08] – jonathan
+++ tech:psql_tips [2010/01/05 07:42] (目前版本) – jonathan
@@ 行 1: / 行 1: @@
+====== PostgreSQL 效能調校技巧 ======
+使用任何 DB , 如果發現 AP 的效能變差, 能夠立即有成效的效能調校就是**檢視 index 的建立與使用狀況**..
+===== - Index 要考慮加上 xxx_pattern_ops =====
+  * 針對非英文語系的資料內容, 要建立的 index 應該要加上 xxx_pattern_ops
+Exp : docmsg -> docsubject 的資料型態是 character varying(1024), 內容主要為中文字, 原本建立的 index 如下:
+<code sql>
+CREATE INDEX docmsg_docsubject_idx
+  ON docmsg
+  USING btree (docsubject);
+</code>
+發現使用以下的語法透過 Explain 是不會用到 docmsg_docsubject_idx 這個 index
+<code sql>
+select docsubject
+  from docmsg
+  where docsubject like '%測試%';
+</code>
+當 index 加上 pattern_ops 之後, 改成如下語法
+<code sql>
+CREATE INDEX docmsg_docsubject_idx
+  ON docmsg
+  USING btree (docsubject varchar_pattern_ops);
+</code>
+在執行一次剛剛的 Explain SQL 指令, 就可以發現可以正確使用到這個 index
+  * 參考資料 : [[http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html|PostgreSQL 手冊]]
+===== - SQL 語法要考慮加上強制指定資料型態 =====
+  * 針對 SQL 內條件部份, 如果比對的資料型態不同, 應該要強制指定
+Exp : xmlboxdocnum2 -> docdate 的資料型態是 character(10), 有建立 index 如下:
+<code sql>
+CREATE INDEX xmlboxdocnum2_docdate_idx
+  ON xmlboxdocnum2
+  USING btree (docdate);
+</code>
+發現使用以下的語法透過 Explain 是不會用到 xmlboxdocnum2_docdate_idx 這個 index
+<code sql>
+select devorgid,count(docdate)
+  from xmlboxdocnum2
+  where docdate >= current_date-7
+  group by devorgid;
+</code>
+當加上 ::character varying 之後, 改成如下語法
+<code sql>
+select devorgid,count(docdate)
+  from xmlboxdocnum2
+  where docdate >= (current_date-7)::character varying
+  group by devorgid;
+</code>
+在執行一次剛剛的 Explain SQL 指令, 就可以發現可以正確使用到這個 index
+===== - 主要欄位不應該允許 NULL =====
+  * 如果要建立 index 的欄位內容出現 NULL , 是不會被加入這個索引資料內容, 所以當查詢條件出現 xxx is NULL 就不會使用到該 index
+===== - 調整 postgresql.conf =====
+當加大 RAM 之後, Exp RAM = 20G 也要一起調大以下參數..
+  * 加大 **shared_buffers** , Exp. <code>shared_buffers = 65536                  # min 16 or max_connections*2, 8KB each</code>
+  * 加大 **effective_cache_size** , Exp. <code>effective_cache_size = 100000           # typically 8KB each</code>
+===== 參考網頁 =====
+  * http://momjian.us/main/writings/pgsql/hw_performance/index.html
+  * http://archives.postgresql.org/pgsql-performance/2007-11/msg00379.php
+{{tag>postgresql db 密技}}