優化查詢匹配數組的前 N 項

我現在正在使用一個充滿國際象棋遊戲數據的 Postgres 數據庫，其中每個遊戲都是“記錄”表中的一行。玩家的移動和這些移動的（可選）電腦評估每個都有自己的列並儲存為數組。

我編寫了一個查詢來檢索指定的開局移動序列的所有評估。（你會認為電腦評估是一致的——但事實並非如此。）開局序列的長度是任意的——可能是一步，也可能是三十。

這是一個範例查詢，它查找以相同的十步開局序列開始的所有遊戲，然後，對於每個帶有評估的遊戲，返回電腦對遊戲中該點的評估 -

SELECT evaluation[10]
FROM records
WHERE moves[1:10]::text[] = ARRAY['b4', 'e5', 'Bb2', 'd6', 'Nf3', 'Nf6', 'g3', 'Bg4', 'Bg2', 'h5']::text[]
AND evaluation IS NOT NULL;

我不確定它是否相關，但移動數據始終是 2-6 個字元的字母數字字元串，並且電腦評估主要是小數（正數和負數），但確實包括偶爾的特殊字元（強制將死有一個 octothorpe前綴）。

這是表格描述的相關片段 -

    Column      |              Type              | 
-----------------+--------------------------------+-
id              | bigint                         | 
moves           | character varying(255)[]       |  
evaluation      | character varying(255)[]       | 
   "records_pkey" PRIMARY KEY, btree (id)
Access method: heap

這是來自 EXPLAIN ANALYZE 的查詢計劃：

Gather  (cost=1000.00..736354.70 rows=905 width=516) (actual time=28251.267..28253.139 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on records  (cost=0.00..735264.20 rows=377 width=516) (actual time=28243.233..28243.234 rows=0 loops=3)
        Filter: ((evaluation IS NOT NULL) AND ((moves[1:10])::text[] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'::text[]))
        Rows Removed by Filter: 971361
Planning Time: 8.275 ms
Execution Time: 28253.915 ms

這太慢了，但我不知道如何優化它——當談到 Postgres 時，我遠非專家，我所有建立索引的嘗試都沒有改變查詢計劃。

一些額外的想法

由於我的開局序列總是從遊戲開始時開始——我可能想在第 1 步到第 3 步或第 1 到 30 步時匹配，但從不從第 7 步到第 15 步進行匹配——我也考慮過將移動數據儲存為以空格分隔的文本字元串，並匹配字元串的開頭。（我也不知道如何優化該查詢，但也許這會更容易。）

雖然這些目前是字元串數組，但我可以將移動和評估都表示為整數數組。（不是我想要的，但是優化這個查詢更重要，如果有幫助，我會做的。）

你怎麼看？我應該從哪裡開始？

您需要快速索引支持。不重新設計你的桌子就很棘手。以下解決方案應該表現出色（微秒而不是秒），但需要一些技巧。係好安全帶。

IMMUTABLE函式表達式索引

只取幾個前導數組元素，比如 8。那應該已經非常有選擇性了。更多只會使索引更大，幾乎不需要額外的過濾。

轉換為字元串。沒有分隔符。這允許誤報，但不太重要。最終，無論如何，我們都會篩選出準確的結果。

IMMUTABLE索引表達式中只允許使用函式。But array_to_string()is only STABLE， not IMMUTABLE，因為它需要anyarray並且某些元素類型沒有不可變的文本表示。我們只處理text（好吧，varchar(255)沒有充分的理由，但都一樣），這實際上是不可變的。但array_to_string()不知道這一點。

所以我們可以“偽造”一個不可變的包裝函式。對於任何可以創建函式的使用者來說，這都是可能的：

CREATE OR REPLACE FUNCTION public.f_8moves(text[])
 RETURNS text
 LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT array_to_string($1[1:8], '')
$func$;

或者，更好的是，直接基於底層的 C 函式定義for only input的誠實IMMUTABLE變體。更快更清潔。讓我們說清楚。這需要超級使用者權限：array_to_string()``text[]``array_to_string_immutable()

-- SET ROLE postgres;  -- you must be superuser

CREATE OR REPLACE FUNCTION public.array_to_string_immutable(text[], text)
RETURNS text
LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS 'array_to_text';

其餘的工作沒有超級使用者權限。

CREATE OR REPLACE FUNCTION public.f_8moves(text[])
 RETURNS text
 LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.array_to_string_immutable($1[1:8], '')
$func$;

有關的：

• PostgreSQL 是否支持“不區分重音”排序規則？

無論哪種方式，我們現在都有一個public.f_8moves(text[])要在以下索引中使用的函式：

CREATE INDEX records_8moves_idx ON records (public.f_8moves(moves) COLLATE "C");

COLLATE "C"正是我們需要允許左錨定（您表達的要求）LIKE表達式。看：

• text_pattern_ops 和 COLLATE “C” 之間有區別嗎？
• 當需要具有運算符類的索引時，唯一索引是否優於唯一約束

如果大部分行有evaluation IS NOT NULL，則將該過濾器添加到索引中，使其成為頂部的部分索引：

CREATE INDEX records_8moves_idx ON records (public.f_8moves(moves) COLLATE "C")
WHERE evaluation IS NOT NULL;

詢問

對於具有10 個或更多數組元素的查詢：

SELECT evaluation[10]
FROM   records
WHERE  public.f_8moves(moves) = public.f_8moves('{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}') COLLATE "C"
AND    moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'
AND    evaluation IS NOT NULL;

COLLATE "C"需要匹配索引。

使用表達式public.f_8moves(moves)來匹配功能索引。表達式的右側可以直接作為字元串給出。但為方便起見，我使用相同的功能。

然後添加原始精確過濾器以將結果縮小到精確匹配：

AND    moves[1:10] = '{b4,e5,Bb2,d6,Nf3,Nf6,g3,Bg4,Bg2,h5}'

看起來多餘，邏輯上是多餘的，但允許使用索引效果很好。

對於少於8 個元素的數組（我們的索引前導），或者通常，使用LIKE左錨定模式：

SELECT evaluation[10]
FROM   records
WHERE  public.f_8moves(moves) LIKE (public.f_8moves('{b4,e5}') || '%') COLLATE "C"  -- COLLATE "C" is optional for LIKE
AND    moves[1:2] = '{b4,e5}'
AND    evaluation IS NOT NULL;

db<>在這裡擺弄

類似，有更多解釋：

• 索引最大行大小錯誤
• Postgres 或 CouchDB 中的全文搜尋？

集群表行

對錶行進行物理排序通常有助於提高性能，這樣每個查詢都可以從一個或幾個數據頁中讀取結果，而不是從各處獲取結果。

使用CLUSTER或 非阻塞替代品pg_repackor pg_squeeze。更多在上面的連結。

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