如果返回一個 數(shù)字或者字符 比較簡單，那么多行多列怎么辦呢，分為以下幾種情況【東西很多，這里只做簡單列舉】
創(chuàng)新互聯(lián)長期為上千余家客戶提供的網(wǎng)站建設服務，團隊從業(yè)經(jīng)驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為東安企業(yè)提供專業(yè)的成都網(wǎng)站制作、網(wǎng)站設計，東安網(wǎng)站改版等技術服務。擁有10年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。
返回多行單列
又分為幾種方式
1. return next，用在 for 循環(huán)中
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOFvarcharas $$DECLARE? ? v_name varchar;BEGINforv_namein( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) ) loop
RETURNNEXT v_name;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 循環(huán)外還有個 return
2. 需要實現(xiàn)聲明 v_name
2. return query，無需 for 循環(huán)
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOFvarcharas $$DECLARE? ? v_rec RECORD;BEGINreturnquery? ( (selectnamefromtest_result1whereid=in_id)union(selectnamefromtest_result2whereid= in_id) );
return;END;$$LANGUAGE PLPGSQL;
注意：如果 返回類型為 setof，最好用如下方法
RETURNQUERYEXECUTESQL
不要這么用
executesqlinto? out;returnout;
返回多行多列
也有多種方式
1. 使用 return next 和? setof record ，需要 for 循環(huán)
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOF RECORDas $$DECLARE? ? v_rec RECORD; BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
RETURNNEXT v_rec;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
注意
1. 讀取表的整行數(shù)據(jù)時才能用 record
2. 如果讀取的數(shù)據(jù)不是整行，需要自定義 復合數(shù)據(jù)類型，否則會報如下錯誤
ERROR:? acolumndefinition listisrequiredforfunctions returning "record"
定義復合類型 ，示例如下
createtype myout2as (
road_num int,
freq bigint);createorreplacefunctiontest(cartext, time1text, time2text)returnssetof myout2as $$declare
array1 text[];
array2 text[];
len1 integer;
len2 integer;
x integer;
y integer;
road_str text;
car_str text;
sql text;
i myout2;
begin-- vin 號拼接selectregexp_split_to_array(car,',')into array2;
selectarray_length(array2,1)into len2;
car_str :='';
y :=1;
whiley= len2 loop
car_str :=car_str||quote_literal(array2[y])||',';
y :=y+1;
end loop;
-- sql 拼接sql :='select road_number, sum(frequency) from heat_map where date_key = '''|| time1
||'-01'' and date_key ='''|| time2
||'-20'' and vin in ('||rtrim(car_str,',')
||')group by road_number;';
--execute sql into out;foriinexecute sql loop
returnnext i;
end loop;
return;end$$ language plpgsql;
在執(zhí)行時可能會報如下錯誤
ERROR:set-valuedfunctioncalledincontext that cannot accept aset
解決方法
select funcname(arg);--改為select*fromfuncname(arg);
2.? return query，無需 for 循環(huán)
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger)RETURNSSETOF RECORDas $$DECLARE? ? v_rec RECORD;BEGINreturnquery? ( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) );
return;END;
$$
LANGUAGE PLPGSQL;
3. 使用 out 輸出參數(shù)
CREATEORREPLACEFUNCTIONfuncname ( in_idinteger,out o_idinteger,out o_namevarchar)
RETURNSSETOF RECORDas $$DECLARE? ? v_rec RECORD;BEGINforv_recin( (selectid , namefromtest_result1whereid=in_id)union(selectid , namefromtest_result2whereid= in_id) )loop
o_id? := v_rec.id;
o_name := v_rec.name;
RETURNNEXT ;
end loop;
return;END;
$$
LANGUAGE PLPGSQL;
總結(jié) - return next return query?
我們可以看到上面無論是單列多行還是多列多行，都用到了 return next 和 return query 方法
在 plpgsql 中，如果存儲過程返回 setof sometype，則返回值必須在 return next 或者 return query 中聲明，然后有一個不帶參數(shù)的 retrun 命令，告訴函數(shù)執(zhí)行完畢；【setof 就意味著 多行】
用法如下
RETURNNEXT expression;RETURN QUERY query;RETURNQUERYEXECUTEcommand-string[ USING expression [, ... ]];
return next 可以用于標量和復合類型數(shù)據(jù)；
return query 命令將查詢到的一條結(jié)果追加到函數(shù)的結(jié)果集中；
二者在單一集合返回函數(shù)中自由混合，在這種情況下，結(jié)果將被級聯(lián)?！居写芯俊?/p>
return query execute 是 return query 的變形，它指定 sql 將被動態(tài)執(zhí)行；
returnqueryselectroad_number,sum(frequency)fromheat_mapgroupbyroad_number;--這樣可以sql :='select road_number, sum(frequency) from heat_map group by road_number';returnquery sql;--這樣不行
參考資料：
自定義類型并返回數(shù)組
return next return query
function返回多列多行
返回結(jié)果集多列和單列的例子??
PostgreSQL存儲過程(1)-基于SQL的存儲過程
動態(tài)SQL
postgresql, pgadmin error RETURN cannot have a parameter in function returning set
存儲過程輸出參數(shù)
PostgreSQL存儲過程(3)-流程控制語句
PostgreSQL命令 EXPLAIN ANALYZE 是日常工作中了解和優(yōu)化SQL查詢過程所用到的最強大工具，后接如 SELECT ... ， UPDATE ... 或者 DELETE ... 等SQL語句，命令執(zhí)行后并不返回數(shù)據(jù)，而是輸出查詢計劃，詳細說明規(guī)劃器通過何種方式來執(zhí)行給定的SQL語句。
下面是從 Postgres Using EXPLAIN 提取的查詢：
它生成的查詢計劃：
Postgres構建了一個規(guī)劃節(jié)點的樹結(jié)構，以表示所采取的不同操作，其中root根和每個 - 指向其中一個操作。在某些情況下， EXPLAIN ANALYZE 會提供除執(zhí)行時間和行數(shù)之外的額外執(zhí)行統(tǒng)計信息，例如上面例子中的 Sort 及 Hash 。除第一個沒有 - 的行之外的任何行都是諸如此類的信息，因此查詢的結(jié)構是：
每個樹分支代表子動作，從里到外以確定哪個是“第一個”發(fā)生（盡管同一級別的節(jié)點順序可能不同）。
在 tenk_unique1 索引上執(zhí)行的第一個操作是 Bitmap Index Scan ：
這對應于SQL WHERE t1.unique1 100 。Postgres查找與條件 unique1 100 匹配的行位置。此處不會返回行數(shù)據(jù)本身。成本估算 (cost=0.00..5.04 rows=101 width=0) 意味著Postgres預期將“花費” 任意計算單位的 5.04 來找到這些行。0.00是此節(jié)點開始工作的成本（在這種情況下，即為查詢的啟動時間）。 rows 是此索引掃描將返回的預估行數(shù)， width 是這些返回行的預估大?。ㄒ宰止?jié)為單位）（0是因為這里只關心位置，而不是行數(shù)據(jù)的內(nèi)容）。
因為使用了 ANALYZE 選項運行 EXPLAIN ，所以查詢被實際執(zhí)行并捕獲了計時信息。 (actual time=0.049..0.049 rows=100 loops=1) 表示索引掃描執(zhí)行了1次（ loops 值），結(jié)果返回了100行，實際時間是0 ..如果節(jié)點執(zhí)行了多次，實際時間是每次迭代的平均值，可以將該值乘以循環(huán)次數(shù)以獲取實際時間?；诔杀镜淖钚?最大時間的概念，范圍值也可能會有所不同。通過這些值，我們可以為該查詢生成一個成本比率，每個成本單位為0.049ms / 5.04單位≈0.01ms/單位。
索引掃描的結(jié)果將傳遞給 Bitmap Heap Scan 操作。在此節(jié)點中，Postgres將獲取別名為t1的tenk1表中行的位置，根據(jù) unique1 100 條件篩選并獲取行。
當乘以之前計算的0.01值時，我們可以得到成本預期的大概時間（229.20 - 5.07）*0.01≈2.24ms，同時每行實際時間為除以4后的結(jié)果：0.526ms。這可能是因為成本估算是取的上限而不是取所有需讀取的行，也或者因為Recheck條件總是生效。
和表順序讀取行（a Seq Scan ）相比， Bitmap Index Scan 和 Bitmap Heap Scan 關聯(lián)操作成本要昂貴得多，但是因為在這種情況下只需要訪問相對較少的行，所以關聯(lián)操作最終會變得更快。通過在獲取行之前將行按照物理順序排序來進一步加速，這會將單獨獲取的成本降到最低。節(jié)點名稱中的“Bitmap”完成了排序操作。
表掃描的結(jié)果（tenk1表中滿足 unique1 100 條件的那些行）將在讀取時被插入到內(nèi)存的哈希表中。正如我們從成本中看到的那樣，這根本不需要時間。
哈希節(jié)點包括散列桶(hash buckets)和批次數(shù)(batches)相關的信息，以及內(nèi)存使用峰值情況。如果批次 1，則還會包括未顯示的磁盤使用信息。內(nèi)存占用在100行* 244字節(jié)= 24.4 kB時是有意義的，它非常接近28kB，我們假定這是哈希鍵本身所占用的內(nèi)存。
接下來，Postgres從別名為t2的tenk2表讀取所有的10000行，并根據(jù)tenk1表行的Hash檢查它們。散列連接意味著將一個表的行輸入到內(nèi)存中的散列（先前的操作中已構建），之后掃描另一個表的行，并根據(jù)散列表探測其值以進行匹配。在第二行可以看到“匹配”的條件， Hash Cond: (t2.unique2 = t1.unique2) 。請注意，因為查詢是從tenk1和tenk2中選擇所有值，所以在散列連接期間每行的寬度加倍。
現(xiàn)在已經(jīng)收集了滿足條件的所有行，可以對結(jié)果集進行排序 Sort Key: t1.fivethous 。
Sort節(jié)點包含排序算法 quicksort 相關的信息 ，排序是在內(nèi)存中還是在磁盤上完成（這將極大地影響速度），以及排序所需的內(nèi)存/磁盤空間量。
熟悉如何解讀查詢計劃會非常有助于優(yōu)化查詢。例如，Seq Scan節(jié)點通常表示添加索引的必要性，讀取速度可能要快得多。
翻譯并編輯，原文出處：
一、使用EXPLAIN：
PostgreSQL為每個查詢都生成一個查詢規(guī)劃，因為選擇正確的查詢路徑對性能的影響是極為關鍵的。PostgreSQL本身已經(jīng)包含了一個規(guī)劃器用于尋找最優(yōu)規(guī)劃，我們可以通過使用EXPLAIN命令來查看規(guī)劃器為每個查詢生成的查詢規(guī)劃。
PostgreSQL中生成的查詢規(guī)劃是由1到n個規(guī)劃節(jié)點構成的規(guī)劃樹，其中最底層的節(jié)點為表掃描節(jié)點，用于從數(shù)據(jù)表中返回檢索出的數(shù)據(jù)行。然而，不同
的掃描節(jié)點類型代表著不同的表訪問模式，如：順序掃描、索引掃描，以及位圖索引掃描等。如果查詢?nèi)匀恍枰B接、聚集、排序，或者是對原始行的其它操作，那
么就會在掃描節(jié)點"之上"有其它額外的節(jié)點。并且這些操作通常都有多種方法，因此在這些位置也有可能出現(xiàn)不同的節(jié)點類型。EXPLAIN將為規(guī)劃樹中的每
個節(jié)點都輸出一行信息，顯示基本的節(jié)點類型和規(guī)劃器為執(zhí)行這個規(guī)劃節(jié)點計算出的預計開銷值。第一行(最上層的節(jié)點)是對該規(guī)劃的總執(zhí)行開銷的預計，這個數(shù)
值就是規(guī)劃器試圖最小化的數(shù)值。
這里有一個簡單的例子，如下：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
EXPLAIN引用的數(shù)據(jù)是：
1). 預計的啟動開銷(在輸出掃描開始之前消耗的時間，比如在一個排序節(jié)點里做排續(xù)的時間)。
2). 預計的總開銷。
3). 預計的該規(guī)劃節(jié)點輸出的行數(shù)。
4). 預計的該規(guī)劃節(jié)點的行平均寬度(單位：字節(jié))。
這里開銷(cost)的計算單位是磁盤頁面的存取數(shù)量，如1.0將表示一次順序的磁盤頁面讀取。其中上層節(jié)點的開銷將包括其所有子節(jié)點的開銷。這里的輸出
行數(shù)(rows)并不是規(guī)劃節(jié)點處理/掃描的行數(shù)，通常會更少一些。一般而言，頂層的行預計數(shù)量會更接近于查詢實際返回的行數(shù)。
現(xiàn)在我們執(zhí)行下面基于系統(tǒng)表的查詢：
復制代碼 代碼如下:
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
從查詢結(jié)果中可以看出tenk1表占有358個磁盤頁面和10000條記錄，然而為了計算cost的值，我們?nèi)匀恍枰懒硗庖粋€系統(tǒng)參數(shù)值。
復制代碼 代碼如下:
postgres=# show cpu_tuple_cost;
cpu_tuple_cost
----------------
0.01
(1 row)
cost = 358(磁盤頁面數(shù)) + 10000(行數(shù)) * 0.01(cpu_tuple_cost系統(tǒng)參數(shù)值)
下面我們再來看一個帶有WHERE條件的查詢規(guī)劃。
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 7000)
EXPLAIN的輸出顯示，WHERE子句被當作一個"filter"應用，這表示該規(guī)劃節(jié)點將掃描表中的每一行數(shù)據(jù)，之后再判定它們是否符合過濾的條
件，最后僅輸出通過過濾條件的行數(shù)。這里由于WHERE子句的存在，預計的輸出行數(shù)減少了。即便如此，掃描仍將訪問所有10000行數(shù)據(jù)，因此開銷并沒有
真正降低，實際上它還增加了一些因數(shù)據(jù)過濾而產(chǎn)生的額外CPU開銷。
上面的數(shù)據(jù)只是一個預計數(shù)字，即使是在每次執(zhí)行ANALYZE命令之后也會隨之改變，因為ANALYZE生成的統(tǒng)計數(shù)據(jù)是通過從該表中隨機抽取的樣本計算的。
如果我們將上面查詢的條件設置的更為嚴格一些的話，將會得到不同的查詢規(guī)劃，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 100)
- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 100)
這里，規(guī)劃器決定使用兩步規(guī)劃，最內(nèi)層的規(guī)劃節(jié)點訪問一個索引，找出匹配索引條件的行的位置，然后上層規(guī)劃節(jié)點再從表里讀取這些行。單獨地讀取數(shù)據(jù)行比順
序地讀取它們的開銷要高很多，但是因為并非訪問該表的所有磁盤頁面，因此該方法的開銷仍然比一次順序掃描的開銷要少。這里使用兩層規(guī)劃的原因是因為上層規(guī)
劃節(jié)點把通過索引檢索出來的行的物理位置先進行排序，這樣可以最小化單獨讀取磁盤頁面的開銷。節(jié)點名稱里面提到的"位圖(bitmap)"是進行排序的機
制。
現(xiàn)在我們還可以將WHERE的條件設置的更加嚴格，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 3)
在該SQL中，表的數(shù)據(jù)行是以索引的順序來讀取的，這樣就會令讀取它們的開銷變得更大，然而事實上這里將要獲取的行數(shù)卻少得可憐，因此沒有必要在基于行的物理位置進行排序了。
現(xiàn)在我們需要向WHERE子句增加另外一個條件，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 3 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 3)
Filter: (stringu1 = 'xxx'::name)
新增的過濾條件stringu1 = 'xxx'只是減少了預計輸出的行數(shù)，但是并沒有減少實際開銷，因為我們?nèi)匀恍枰L問相同數(shù)量的數(shù)據(jù)行。而該條件并沒有作為一個索引條件，而是被當成對索引結(jié)果的過濾條件來看待。
如果WHERE條件里有多個字段存在索引，那么規(guī)劃器可能會使用索引的AND或OR的組合，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 100 AND unique2 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 100) AND (unique2 9000))
- BitmapAnd (cost=11.27..11.27 rows=11 width=0)
- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 100)
- Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 9000)
這樣的結(jié)果將會導致訪問兩個索引，與只使用一個索引，而把另外一個條件只當作過濾器相比，這個方法未必是更優(yōu)。
現(xiàn)在讓我們來看一下基于索引字段進行表連接的查詢規(guī)劃，如：
復制代碼 代碼如下:
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 100)
- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 100)
- Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
從查詢規(guī)劃中可以看出(Nested
Loop)該查詢語句使用了嵌套循環(huán)。外層的掃描是一個位圖索引，因此其開銷與行計數(shù)和之前查詢的開銷是相同的，這是因為條件unique1
100發(fā)揮了作用。 這個時候t1.unique2 =
t2.unique2條件子句還沒有產(chǎn)生什么作用，因此它不會影響外層掃描的行計數(shù)。然而對于內(nèi)層掃描而言，當前外層掃描的數(shù)據(jù)行將被插入到內(nèi)層索引掃描
中，并生成類似的條件t2.unique2 = constant。所以，內(nèi)層掃描將得到和EXPLAIN SELECT * FROM tenk2
WHERE unique2 = 42一樣的計劃和開銷。最后，以外層掃描的開銷為基礎設置循環(huán)節(jié)點的開銷，再加上每個外層行的一個迭代(這里是 106
* 3.01)，以及連接處理需要的一點點CPU時間。
如果不想使用嵌套循環(huán)的方式來規(guī)劃上面的查詢，那么我們可以通過執(zhí)行以下系統(tǒng)設置，以關閉嵌套循環(huán)，如：
復制代碼 代碼如下:
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: ("outer".unique2 = "inner".unique2)
- Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
- Hash (cost=232.35..232.35 rows=106 width=244)
- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 100)
- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 100)
這個規(guī)劃仍然試圖用同樣的索引掃描從tenk1里面取出符合要求的100行，并把它們存儲在內(nèi)存中的散列(哈希)表里，然后對tenk2做一次全表順序掃
描，并為每一條tenk2中的記錄查詢散列(哈希)表，尋找可能匹配t1.unique2 =
t2.unique2的行。讀取tenk1和建立散列表是此散列聯(lián)接的全部啟動開銷，因為我們在開始讀取tenk2之前不可能獲得任何輸出行。
此外，我們還可以用EXPLAIN ANALYZE命令檢查規(guī)劃器預估值的準確性。這個命令將先執(zhí)行該查詢，然后顯示每個規(guī)劃節(jié)點內(nèi)實際運行時間，以及單純EXPLAIN命令顯示的預計開銷，如：
復制代碼 代碼如下:
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 100)
- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37
rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 100)
- Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1
loops=100)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 14.452 ms
注意"actual time"數(shù)值是以真實時間的毫秒來計算的，而"cost"預估值是以磁盤頁面讀取數(shù)量來計算的，所以它們很可能是不一致的。然而我們需要關注的只是兩組數(shù)據(jù)的比值是否一致。
在一些查詢規(guī)劃里，一個子規(guī)劃節(jié)點很可能會運行多次，如之前的嵌套循環(huán)規(guī)劃，內(nèi)層的索引掃描會為每個外層行執(zhí)行一次。在這種情況下，"loops"將報告
該節(jié)點執(zhí)行的總次數(shù)，而顯示的實際時間和行數(shù)目則是每次執(zhí)行的平均值。這么做的原因是令這些真實數(shù)值與開銷預計顯示的數(shù)值更具可比性。如果想獲得該節(jié)點所
花費的時間總數(shù)，計算方式是用該值乘以"loops"值。
EXPLAIN ANALYZE顯示的"Total runtime"包括執(zhí)行器啟動和關閉的時間，以及結(jié)果行處理的時間，但是它并不包括分析、重寫或者規(guī)劃的時間。
如果EXPLAIN命令僅能用于測試環(huán)境，而不能用于真實環(huán)境，那它就什么用都沒有。比如，在一個數(shù)據(jù)較少的表上執(zhí)行EXPLAIN，它不能適用于數(shù)量很
多的大表，因為規(guī)劃器的開銷計算不是線性的，因此它很可能對大些或者小些的表選擇不同的規(guī)劃。一個極端的例子是一個只占據(jù)一個磁盤頁面的表，在這樣的表
上，不管它有沒有索引可以使用，你幾乎都總是得到順序掃描規(guī)劃。規(guī)劃器知道不管在任何情況下它都要進行一個磁盤頁面的讀取，所以再增加幾個磁盤頁面讀取用
以查找索引是毫無意義的。
二、批量數(shù)據(jù)插入：
有以下幾種方法用于優(yōu)化數(shù)據(jù)的批量插入。
1. 關閉自動提交：
在批量插入數(shù)據(jù)時，如果每條數(shù)據(jù)都被自動提交，當中途出現(xiàn)系統(tǒng)故障時，不僅不能保障本次批量插入的數(shù)據(jù)一致性，而且由于有多次提交操作的發(fā)生，整個插入效
率也會受到很大的打擊。解決方法是，關閉系統(tǒng)的自動提交，并且在插入開始之前，顯示的執(zhí)行begin
transaction命令，在全部插入操作完成之后再執(zhí)行commit命令提交所有的插入操作。
2. 使用COPY：
使用COPY在一條命令里裝載所有記錄，而不是一系列的INSERT命令。COPY命令是為裝載數(shù)量巨大的數(shù)據(jù)行優(yōu)化過的，它不像INSERT命令那樣靈
活，但是在裝載大量數(shù)據(jù)時，系統(tǒng)開銷也要少很多。因為COPY是單條命令，因此在填充表的時就沒有必要關閉自動提交了。
3. 刪除索引：
如果你正在裝載一個新創(chuàng)建的表，最快的方法是創(chuàng)建表，用COPY批量裝載，然后創(chuàng)建表需要的任何索引。因為在已存在數(shù)據(jù)的表上創(chuàng)建索引比維護逐行增加要快。當然在缺少索引期間，其它有關該表的查詢操作的性能將會受到一定的影響，唯一性約束也有可能遭到破壞。
4. 刪除外鍵約束：
和索引一樣，"批量地"檢查外鍵約束比一行行檢查更加高效。因此，我們可以先刪除外鍵約束，裝載數(shù)據(jù)，然后在重建約束。
5. 增大maintenance_work_mem：
在裝載大量數(shù)據(jù)時，臨時增大maintenance_work_mem系統(tǒng)變量的值可以改進性能。這個系統(tǒng)參數(shù)可以提高CREATE
INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的執(zhí)行效率，但是它不會對COPY操作本身產(chǎn)生多大的影響。
6. 增大checkpoint_segments：
臨時增大checkpoint_segments系統(tǒng)變量的值也可以提高大量數(shù)據(jù)裝載的效率。這是因為在向PostgreSQL裝載大量數(shù)據(jù)時，將會導致
檢查點操作(由系統(tǒng)變量checkpoint_timeout聲明)比平時更加頻繁的發(fā)生。在每次檢查點發(fā)生時，所有的臟數(shù)據(jù)都必須flush到磁盤上。
通過提高checkpoint_segments變量的值，可以有效的減少檢查點的數(shù)目。
7. 事后運行ANALYZE：
在增加或者更新了大量數(shù)據(jù)之后，應該立即運行ANALYZE命令，這樣可以保證規(guī)劃器得到基于該表的最新數(shù)據(jù)統(tǒng)計。換句話說，如果沒有統(tǒng)計數(shù)據(jù)或者統(tǒng)計數(shù)據(jù)太過陳舊，那么規(guī)劃器很可能會選擇一個較差的查詢規(guī)劃，從而導致查詢效率過于低下。
重慶分公司
重慶分公司
