Postgres 中的數組包含查詢
關於數組上 GIN 索引的問題。
我的工作表中有 200 萬行。我需要根據使用者擁有的技能以及最高優先級的工作來找到使用者可以做的工作。使用者總是會有更多的技能。
從 RDMS 標準方式開始，但查詢的性能很差，因此在搜尋其他選項時發現 postgres 支持數組包含查詢，並且數組也可以被索引。
桌子：
CREATE TABLE work ( work_id TEXT DEFAULT nextval('work_id_seq'::regclass) NOT NULL, priority_score BIGINT NOT NULL, work_data JSONB, created_date TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, current_status CHARACTER VARYING, PRIMARY KEY (work_id) );指數：
CREATE INDEX test_gin_1 ON work USING gin(jsonarray2intarray((work_data ->> 'skills'::text); Function: CREATE OR REPLACE FUNCTION jsonarray2intarray" (text) RETURNS integer[] IMMUTABLE AS $body$ SELECT translate($1, '[]', '{}')::int[] $body$ LANGUAGE sql樣本數據：
282941 1564 {“技能”：
$$ 213, 311, 374, 554 $$} 使用以下查詢，響應速度很慢。技能數組只有一條記錄，254,336,391,485
with T as ( SELECT work_id, priority_score, current_status, work_data FROM work WHERE jsonarray2intarray( work.work_data ->> 'skills') <@ '{254,336,391,485 }' AND work.current_status = 'ASSIGNABLE' ORDER BY priority_score DESC, created_date ) select * from t LIMIT 1 FOR UPDATE skip lockedLimit (cost=45095.54..45095.56 rows=1 width=296) (actual time=3776.169..3776.170 rows=1 loops=1) Output: t.work_id,t.priority_score, t.current_status,t.work_data CTE t -> Sort (cost=45059.29..45095.54 rows=14503 width=325) (actual time=3776.166..3776.166 rows=1 loops=1) Output: work.work_id,work.priority_score, work.current_status,work.work_data Sort Key: work.priority_score DESC, work.created_date Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on work (cost=524.44..41872.83 rows=14503 width=325) (actual time=37.718..3776.159 rows=1 loops=1) Output: work.work_id,work.priority_score, work.current_status,work.work_data Recheck Cond: (jsonarray2intarray((work.work_data ->> 'skills'::text)) <@ '{254,336,391,485}'::integer[]) Rows Removed by Index Recheck: 1072296 Filter: ((work.current_status)::text = 'ASSIGNABLE'::text) Heap Blocks: exact=41243 lossy=26451 -> Bitmap Index Scan on test_gin_1 (cost=0.00..520.81 rows=14509 width=0) (actual time=30.699..30.699 rows=154888 loops=1) Index Cond: (jsonarray2intarray((work.work_data ->> 'skills'::text)) <@ '{254,336,391,485}'::integer[]) -> CTE Scan on t (cost=0.00..290.06 rows=14503 width=296) (actual time=3776.168..3776.168 rows=1 loops=1) Output: t.work_id,t.priority_score, t.current_status,t.work_data Planning time: 0.161 ms Execution time: 3776.202 ms具有不同輸入的相同查詢很快。技能 101、103 大約有 26K 條記錄：
with T as ( SELECT work_id, priority_score, current_status, work_data FROM work WHERE jsonarray2intarray( work.work_data ->> 'skills') <@ '{101, 103 }' AND work.current_status = 'ASSIGNABLE' ORDER BY priority_score DESC, created_date ) select * from t LIMIT 1 FOR UPDATE skip lockedLimit (cost=45076.55..45076.57 rows=1 width=296) (actual time=116.185..116.186 rows=1 loops=1) Output: t.work_id,t.priority_score, t.current_status,t.work_data CTE t -> Sort (cost=45040.26..45076.55 rows=14513 width=325) (actual time=116.182..116.182 rows=1 loops=1) Output: work.work_id,work.priority_score, work.current_status,work.work_data Sort Key: work.priority_score DESC, work.created_date Sort Method: external merge Disk: 8088kB -> Bitmap Heap Scan on work (cost=476.52..41853.05 rows=14513 width=325) (actual time=9.223..94.591 rows=26301 loops=1) Output: work.work_id,work.priority_score, work.current_status,work.work_data Recheck Cond: (jsonarray2intarray((workd.work_data ->> 'skills'::text)) <@ '{101,103}'::integer[]) Filter: ((workd.current_status)::text = 'ASSIGNABLE'::text) Rows Removed by Filter: 1357 Heap Blocks: exact=2317 -> Bitmap Index Scan on test_gin_1 (cost=0.00..472.89 rows=14519 width=0) (actual time=4.638..4.638 rows=39871 loops=1) Index Cond: (jsonarray2intarray((workd.work_data ->> 'skills'::text)) <@ '{101,103}'::integer[]) -> CTE Scan on t (cost=0.00..290.26 rows=14513 width=296) (actual time=116.184..116.184 rows=1 loops=1) Output: t.work_id,t.priority_score, t.current_status,t.work_data Planning time: 0.160 ms Execution time: 117.278 ms我正在尋找有關獲得一致響應的建議。
注意： 不是 postgres 特定的方法：
查詢大約需要 40 到 50 秒，這非常糟糕
除了上面定義的工作表之外，我還添加了一個新表 work_data
CREATE TABLE work_data ( skill_id bigint, work_id bigint )詢問：
select work.id from work inner join work_data on (work.id=work_data.work_id) group by work.id having sum(case when work_data.skill_id in (2269,3805,828,9127) then 0 else 1 end)=0
在這個方向上索引包含本質上是麻煩的。如果這樣做
column @> const_array，它可以獲取“列”中最稀有的 const_array 元素，並僅檢查具有該稀有值的行以確保它們也具有所有其餘部分。但要做到這一點
column <@ const_array，它必須檢查包含 const_array 中列出的任何值的所有行，以及所有具有“列”空列表的行。這可能需要檢查很多行，除非 const_array 中列出的每個技能都是罕見的。此外，通過將數組儲存為 JSONB 並動態轉換為 postgresql 數組，您在這裡並沒有給自己帶來任何好處。這將使您必須做的任何重新檢查都變得格外昂貴。為什麼不創建一個 int 類型的列
$$ $$直接在表中，而不是用 JSONB 包裝？ 我認為您在這裡遇到了業務問題以及數據庫問題。如果某人有一項稀有技能，他們應該研究需要該技能的項目，而不是幾乎任何人都能做到的簡單項目。然後只有在沒有需要稀有技能的情況下才使用簡單的項目。解決這種業務問題修改的查詢對於數據庫來說應該更快地實現，並且更有用。
我看到了多個問題。jjanes和Laurenz已經指出了一些問題。除了這些，我還有一些評論。
在返回前 1（或少數）行之前將
WHERE子句中的謂詞與不相關的謂片語合。ORDER BY很難優化。這個相關的問題和答案應該會有所幫助：還：
LIMIT 1 FOR UPDATE 跳過鎖定
由於您只想返回單行，因此不要將帶有大量結果集的查詢包裝在 CTE 中，而只是在外部查詢中選擇單行。這是所有其他問題之上的最壞情況。在 Postgres 12 之前，CTE 總是物化的，在你的情況下真的不需要。改為合併到單個查詢中：
SELECT work_id, priority_score, current_status, work_data FROM work WHERE jsonarray2intarray(work.work_data ->> 'skills') <@ '{254,336,391,485 }' AND current_status = 'ASSIGNABLE' ORDER BY priority_score DESC, created_date LIMIT 1 FOR UPDATE SKIP LOCKED;
ORDER BY為了進行更多優化， (priority_score, )中的列上的任何可用元數據都created_date可能有用。餐桌設計
你有（我添加了評論）：
CREATE TABLE public.work( work_id TEXT DEFAULT nextval('work_id_seq'::regclass) NOT NULL, -- text is nonsense. Use integer. bigint, if you must (I doubt it) priority_score BIGINT NOT NULL, -- why bigint? while PK is int, this seems uncalled for work_data JSONB, -- very inefficient, as was pointed out. -- and it reeks of a design error that this column can be NULL created_date TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL, -- typically you really want timestamptz current_status CHARACTER VARYING, -- should probably be an enum or FK column referencing a lookup table -- or something smaller for only few possible states. PRIMARY KEY (work_id) )如果
work_data可以為 NULL，則該行永遠不會匹配。如果您允許空數組 (’{}’)，則該行始終匹配. 兩者似乎都沒有意義。這樣的事情已經很有幫助了（列的順序也很重要）：
CREATE TABLE public.work ( work_id serial PRIMARY KEY , priority_score int NOT NULL, -- ? , created_date timestamptz NOT NULL DEFAULT now() , work_data int[] NOT NULL CHECK (work_data <> '{}'), , current_status int, -- ? );正常化？
至於你的標準化嘗試：
CREATE TABLE public.work_data ( skill_id bigint, -- I doubt bigint makes sense, int work_id bigint -- again, probably just int )改為考慮：
CREATE TABLE public.work_data ( skill_id int , work_id int , PRIMARY KEY (skill_id, work_id) -- columns in *this* order );根據使用的查詢，您可能需要也可能不需要
(work_id, skill_id). 看：如果不加入到以下位置，您的查詢會更快
work：SELECT work_id FROM work_data GROUP BY 1 HAVING count(*) FILTER (WHERE skill_id NOT IN (2269,3805,828,9127)) = 0但這應該會更快，但是：
SELECT id FROM work w WHERE NOT EXISTS ( SELECT FROM work_data WHERE work_id = w.id AND skill_id NOT IN (2269,3805,828,9127) );在您的原始文件中， Postgres必須計算
work.NOT EXISTS可以在發現第一個違規行後立即停止。更快，但仍然不是很快。而且由於
ORDER BY缺少您在第一個查詢中顯示的內容，因此它也不等效。而且還不清楚你首先需要什麼……所以這是複雜的東西。需要有經驗的人。從我們到目前為止討論的所有內容來看，那個人可能不是你（沒有冒犯）。考慮專業幫助。