PostgreSQL使用函數的多表相關性檢視在排序時的效能問題

標籤：plpgsql   關聯   好的   一個   ace   off   索引   函數   pre   

一、問題描述

近日PostgreSQL的某個表的記錄數由萬級增加到一百萬級（設計能力是一億）時，建立在該表之上的某個多表關聯VIEW的查詢效能急劇變慢（大約從10ms級躍升到100s級）。經分析查詢計劃，發現瓶頸在於排序用時很長；而排序用時的誘因是什嗎？在排除掉一個個其它因素後，發現是VIEW定義中使用了函數（非內建函式，特此說明）。在將自訂函數更改為等效的子查詢或串連查詢時，效能得到很好的改善。

因為實際的表、VIEW、函數都過於複雜，為描述及重現問題的關鍵，以下將表、VIEW、函數等都進行簡化，。

二、測試對象

建立三個測試用表：

create table t1(  id int not null primary key,  name varchar(20));create table t2(  id int not null primary key,  name varchar(20));create table t3(  id int not null primary key,  name varchar(20));

再建立函數、包含它的VIEW、作為對比的等效VIEW：

create or replace function f1(p_id in int)  returns int as $$  declare v_name varchar(40);begin  select name3 into v_name from t3 where id=p_id;  if v_name is null then return 0; else return length(v_name); end if;end;$$ language plpgsql;create view v1 as  select t1.id id, t1.name name1, t2.name name2, f1(t1.id) length  from t1 left join t2 on t2.id=t1.id;

  create view v2 as
    select t1.id id, t1.name1 name1, t2.name2 name2,
      coalesce(length(t3.name3), 0) length
    from t1 left join t2 on t2.id=t1.id left join t3 on t3.id=t1.id;

最後向表t1插入8000條記錄，id為1..8000，name的值則無所謂；再分別拷貝部分資料到表t2、t3：

insert into t2 select * from t1 where t1.id%2=0;insert into t3 select * from t1 where t1.id%3=0;

三、檢查執行計畫

完成所有準備工作後，檢查在兩個VIEW上排序查詢的執行計畫：

顯然，排序用時是大頭，而v1在排序上的用時差不多是v2的3倍。

再進一步測試，如果加上limit ... offset（常跟order by一起用於分頁），兩個VIEW的差距更加明顯：

如果去掉排序操作，v1的查詢用時也會多於v2，但相比之前少差不多一個數量級。限於篇幅，這裡不貼圖了。

本次測試的記錄數還不到萬級，已經可以看出兩種VIEW的差距，有興趣的同行可以自行驗證表記錄數在十萬級及更多以上的情況。

四、結論

根據本次簡單測試，並結合實踐中的情況（抱歉不能給出詳情），得出以下結論：

1. PostgreSQL中，如果多表關聯VIEW中包使用了函數，盡量修改為子查詢或串連查詢；
2. 對於排序操作，含函數的VIEW的效能遠低於等效的子查詢或串連查詢的VIEW，如再含有分頁操作，兩者的效能差距更大；
3. 實踐中，當主表記錄數達到一定數量（臨界值未知），含函數的VIEW的排序甚至無法利用索引而走全表掃描，從而引發用時劇增；
4. 以前的實踐中，Oracle中兩種VIEW的差別並不是十分明顯。

PostgreSQL使用函數的多表相關性檢視在排序時的效能問題