使用函式比執行查詢慢得多

我正在嘗試編寫一個 plpgsql 函式，該函式執行一個查詢，其中WHERE部分是使用 EXECUTE 可變的，如下所示：

CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$
   DECLARE
       q TEXT;
   BEGIN
       q = FORMAT('SELECT * FROM Bin WHERE %s;', p_filter);
       RETURN QUERY EXECUTE q;
   END
$$ LANGUAGE plpgsql;

該函式的執行速度比直接使用查詢慢得多，因此我嘗試創建一個函式，在該函式中我會像這樣返回查詢：

CREATE FUNCTION test2() RETURNS SETOF Bin AS $$
   BEGIN
       RETURN QUERY SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
   END
$$ LANGUAGE plpgsql;

在這兩種情況下，使用 auto_explain 我得到了這個計劃：

2021-01-12 11:18:14.524 CET [19356] postgres@astar LOG:  duration: 26.203 ms  plan:
   Query Text: SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
   Seq Scan on bin  (cost=0.00..3779.06 rows=1 width=181) (actual time=0.008..22.763 rows=72687 loops=1)
     Filter: (((binfrac).linedivisionnb = 0) AND ((binfrac).columndivisionnb = 0) AND (_weight = 0) AND ((binfrac).height = 64))
     Rows Removed by Filter: 14316
2021-01-12 11:18:14.524 CET [19356] postgres@astar CONTEXT:  PL/pgSQL function test1(text) line 6 at RETURN QUERY
2021-01-12 11:18:14.532 CET [19356] postgres@astar LOG:  duration: 45.242 ms  plan:
   Query Text: EXPLAIN ANALYZE SELECT * FROM test1('((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0)');
   Function Scan on test1  (cost=0.25..10.25 rows=1000 width=511) (actual time=36.904..43.560 rows=72687 loops=1)

直接或在 SQL STABLE 函式中執行查詢時，我只得到第一部分：

2021-01-12 11:25:30.976 CET [19356] postgres@astar LOG:  duration: 27.241 ms  plan:
   Query Text: EXPLAIN ANALYZE SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
   Seq Scan on bin  (cost=0.00..3779.06 rows=1 width=181) (actual time=0.009..25.511 rows=72687 loops=1)
     Filter: (((binfrac).linedivisionnb = 0) AND ((binfrac).columndivisionnb = 0) AND (_weight = 0) AND ((binfrac).height = 64))
     Rows Removed by Filter: 14316

額外的 20 毫秒從何而來？有沒有辦法消除這種成本或至少減輕它？

額外的時間用於plpgsql的成本，這不小。

使用 RETURN QUERY，它首先將所有元組讀入一個元組儲存，然後再次將它們讀回。在這種情況下，我認為這佔了大部分額外時間。請注意，此額外成本將與返回行的方式成正比（而不是檢查多少行，然後過濾掉）。

引用自：https://dba.stackexchange.com/questions/283051