為什麼 SELECT（在正則表達式上加入）在 +1 行時會慢 30 倍（查詢計劃保持不變）？記憶體不足？

這是我的查詢，需要2.7 秒：

SELECT t2.id, t1.id FROM sample t1
INNER JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex;

相關EXPLAIN (ANALYSE,BUFFERS)輸出：

Nested Loop (cost=0.00..1108.30 rows=356 width=8) (actual time=56.130..2740.906 rows=33 loops=1)
Join Filter: (t1.data ~* regex_set.regex)
Rows Removed by Join Filter: 65967
Buffers: local hit=18
-> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.014..1.534 rows=2000 loops=1)
   Buffers: local hit=17
-> Materialize (cost=0.00..1.08 rows=33 width=36) (actual time=0.000..0.004 rows=33 loops=2000)
   Buffers: local hit=1
   -> Limit (cost=0.00..0.59 rows=33 width=36) (actual time=0.005..0.014 rows=33 loops=1)
      Buffers: local hit=1
      -> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.004..0.008 rows=33 loops=1)
         Buffers: local hit=1
Planning time: 0.129 ms
Execution time: 2740.952 ms

限制設置為 32 的相同查詢使用相同的計劃僅需0.25 秒（第 33 行沒有什麼特別之處：經過測試limit 32 offset 1，結果與以下相同）：

Nested Loop (cost=0.00..1075.88 rows=345 width=8) (actual time=5.871..255.315 rows=32 loops=1)
Join Filter: (t1.data ~* regex_set.regex)
Rows Removed by Join Filter: 63968
Buffers: local hit=18
-> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.008..0.498 rows=2000 loops=1)
   Buffers: local hit=17
-> Materialize (cost=0.00..1.05 rows=32 width=36) (actual time=0.000..0.002 rows=32 loops=2000)
   Buffers: local hit=1
   -> Limit (cost=0.00..0.57 rows=32 width=36) (actual time=0.003..0.013 rows=32 loops=1)
      Buffers: local hit=1
      -> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.003..0.006 rows=32 loops=1)
         Buffers: local hit=1
Planning time: 0.109 ms
Execution time: 255.374 ms  

這是我用於這些測試的初始化腳本。

DO $$
 DECLARE s_length INT=2000;
 DECLARE r_length INT=50;
BEGIN
   DROP TABLE IF EXISTS sample;
   CREATE TEMP TABLE sample AS
     SELECT generate_series(1, s_length) id, md5(random() :: text) AS data;

   DROP TABLE IF EXISTS regex_set;
   CREATE TEMP TABLE regex_set AS
     SELECT g.id, s.data regex
     FROM generate_series(1, r_length) g (id),
          sample s
     WHERE s.id = round(g.id * s_length / r_length);
END $$;

使用我的真實數據，我有一個**完全相同的33 limit**延遲（時差是 1 到 30）。

我在 Docker 容器（2 cpu / 2GB RAM）上執行 Postgres 10，並對預設 postgres.conf 進行了以下（任意）編輯（沒有太大影響）：

shared_buffers = 512MB
temp_buffers = 32MB
work_mem = 16MB (also set to 64MB, had no effect)

預設 MySQL 5.8 容器上的等效查詢要快得多。

是否有任何 Postgres 大師可以解釋正在發生的事情？

感謝您提供完整的範例。有了這個和“perf”和“gdb”的一點幫助，我將問題追溯到這個：

src/backend/utils/adt/regexp.c:#define MAX_CACHED_RES   32

一旦您嘗試在工作記憶體中保存 33 個正則表達式並以可預測的周期訪問它們，每個正則表達式都會從記憶體中推出下一個將需要的正則表達式，因此每次需要時都會重新編譯每個正則表達式。這種重新編譯很慢。

我不知道為什麼選擇 32 作為該值，但顯然記憶體不能無限大小。也許最好是使用者可設置而不是編譯。

有了這些知識，就很容易調整查詢以更改連接執行的順序，以便在繼續下一個正則表達式之前測試一個正則表達式完成：

SELECT t2.id, t1.id FROM sample t1
right JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex

但是，如果 t2 中的正則表達式與 t1 中的任何行都不匹配，則此查詢將開始為這些行返回空擴展結果，這是現有查詢的行為變化。如果您嘗試添加一個簡單的 WHERE 子句來過濾掉那些額外的行，那麼計劃器將看穿您的技巧並返回使用慢速連接順序。您可以嘗試使用不透明的等價物將它們過濾掉：

SELECT t2.id, t1.id FROM sample t1
right JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex 
where coalesce(t1.id,-5) != -5

（假設真正的 t1.id 總是積極的）

但也許這個問題的最佳解決方案是建構一個支持正則表達式查詢的專業索引：

create extension pg_trgm ;
create index on sample using gin (data gin_trgm_ops);

使用該索引的前景將固有地要求使用更快的連接順序，並且索引的實際使用也會使查詢更快。

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