使用 format() 動態建構的 query_int 的查詢計劃
為了簡單起見，這裡有一個靜態建構的查詢版本，執行速度非常快：
SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20;問題是標籤 ID 保存在另一個表中，當然應用程序的 UI 接受標籤作為字元串。所以實際的查詢看起來像這樣：
SELECT * FROM media WHERE tag_ids @@ (SELECT format('%s', ( SELECT ARRAY_TO_STRING(( SELECT ARRAY( WITH tag_ids as (SELECT id FROM tags WHERE tag IN ('kittens', 'puppies') ) SELECT id FROM tag_ids UNION SELECT 0 FROM tag_ids WHERE NOT EXISTS (SELECT 1 FROM tag_ids) )), '&') )))::query_int ORDER BY rating DESC LIMIT 20;所以問題是查詢計劃器總是預測該
tag_ids @@ ...子句將返回大約 32k 行。考慮到有LIMIT20 個，然後在 上進行索引掃描rating，然後進行順序掃描是完全有意義的。但！一些，實際上是大多數，標籤過濾器返回的方式比這少，使得索引掃描有問題，緩慢，例如：-> Index Scan using media_rating_index on media (cost=0.43..2741321.93 rows=32509 width=11) (actual time=70.816..24270.968 rows=20 loops=1) Filter: (tag_ids @@ ($5)::query_int) Rows Removed by Filter: 1565931 Planning Time: 0.514 ms Execution Time: 24271.155 ms當然，當
tag_ids @@ ...對已知發生率較低的標籤使用靜態建構的子句時，查詢計劃器會選擇基於標籤的索引，例如：Limit (cost=9163.00..9163.05 rows=20 width=11) (actual time=3.838..3.841 rows=3 loops=1) -> Sort (cost=9163.00..9168.96 rows=2384 width=11) (actual time=3.834..3.836 rows=3 loops=1) Sort Key: rating DESC Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on media (cost=38.48..9099.57 rows=2384 width=11) (actual time=2.780..3.796 rows=3 loops=1) Recheck Cond: (tag_ids @@ '654&321'::query_int) Heap Blocks: exact=3 -> Bitmap Index Scan on media_tag_ids_gin__int_ops_index (cost=0.00..37.88 rows=2384 width=0) (actual time=2.023..2.023 rows=3 loops=1) Index Cond: (tag_ids @@ '654&321'::query_int) Planning Time: 2.218 ms Execution Time: 3.952 ms解決此問題的一種方法是僅從應用程序向數據庫發送 2 個查詢，然後
format()甚至可以在應用程式碼中完成。但這只是感覺有點像放棄。另一件事是 SQL 函式，但這似乎有點過頭了。是否有另一種更慣用的方式為查詢規劃器提供標籤 ID，以便它可以更好地預測返回的行數，從而規劃更好的查詢？
首先，您可以簡化您的查詢。
format()不需要，除其他外：SELECT * FROM media WHERE tag_ids @@ array_to_string(ARRAY ( WITH tag_ids AS (SELECT id FROM tags WHERE tag IN ('kittens', 'puppies')) TABLE tag_ids UNION ALL SELECT 0 WHERE NOT EXISTS (TABLE tag_ids) ), '&')::query_int ORDER BY rating DESC LIMIT 20;雖然您的
query_int值僅使用&，但進一步簡化為：SELECT * FROM media WHERE tag_ids @> COALESCE(NULLIF(ARRAY(SELECT id FROM tags WHERE tag IN ('kittens', 'puppies')), '{}'), '{0}') ORDER BY rating DESC LIMIT 20;應該已經更快了。但核心問題仍然存在 - 這與此處詳細討論的問題非常相似：
基本上是這樣的：Postgres（必須）根據不完整的資訊選擇查詢計劃。列統計資訊不能包含許多稀有值的所有頻率。所以很難估計哪個計劃會更快：
- 方案A：遍歷索引
media_rating_index和過濾器- 計劃B：點陣圖索引掃描
media_tag_ids_gin__int_ops_index，按等級排序，限制您的案例增加了複雜子查詢的複雜性，這使得估計可能會產生什麼變得更加困難。
無論您做什麼，請確保您執行可用的最新版本的 Postgres，因為您的特定問題很可能會從最近的一些改進中受益。
根據未公開的資訊，其中之一可能有效：
1.
LEFT JOIN LATERAL如參考答案所示，重寫您的查詢。2.
通過混淆. _
ORDER BY假設它是一個數字列：SELECT ... ORDER BY rating + 0 DESC LIMIT 20;就像 Laurenz 建議的那樣：
3.
更激進的是，刪除索引
media_rating_index- 除非其他查詢需要它！4.
Jeff 建議在相關情況下手動設置
n_distinct，但我不確定如何為數組元素實現相同的設置……5.
這是pgsql-performance list 中的一個相關案例。您的情況更簡單，您可以通過提高統計目標來改善您的情況
tag_ids：ALTER TABLE media ALTER COLUMN tag_ids SET STATISTICS 1000;然後執行
ANALYZE media。除了
其他選項之外， 這可能很有用，尤其是以下選項。6.
在一個相關案例中，我使用 fake-IMMUTABLE 函式取得了成功：
CREATE FUNCTION f_tag_id_array(VARIADIC text[]) RETURNS int[] IMMUTABLE PARALLEL SAFE LANGUAGE plpgsql AS $func$ BEGIN RETURN COALESCE(NULLIF(ARRAY(SELECT id FROM tags WHERE tag = ANY($1)), '{}'), '{0}'); END $func$然後在您的查詢中使用它：
SELECT * FROM media WHERE tag_ids @> f_tag_id_array('kittens', 'puppies') ORDER BY rating DESC LIMIT 20;由於 Postgres 現在假定它
f_tag_id_array()是不可變的，它可能會選擇提前評估它並繼續返回的常量 - 從而為您所追求的已知稀有 ID 實現不同的查詢計劃。請注意，我們在撒謊
IMMUTABLE——事實並非如此。所以不要在上面建立索引。並且在 table 上使用並發寫訪問是不安全的tags。而且由於我們躺在那裡，函式不能被內聯，所以在這種情況下使用更簡單的 SQL 函式不會買任何東西。而是擁有 plpgsql 的已保存查詢計劃功能。
在
STABLEPostgres 11 實例上也是如此，而 Postgres 10 實例需要IMMUTABLEhack …