為什麼這個帶有 WHERE、ORDER BY 和 LIMIT 的查詢這麼慢？

鑑於此表posts_lists：

表“public.posts_lists”
專欄 | 類型 | 整理 | 可空 | 預設
------------+------------------------+-----------+----------+---------
編號 | 字元變化(20) | | 不為空 |
使用者 ID | 字元變化(20) | | |
標籤 | jsonb | | |
分數 | 整數 | | |
created_at | 整數 | | |
索引：
"tmp_posts_lists_pkey1" 主鍵，btree (id)
“tmp_posts_lists_idx_create_at1532588309” btree (created_at)
"tmp_posts_lists_idx_score_desc1532588309" btree (score_rank(score, id::text) DESC)
“tmp_posts_lists_idx_tags1532588309”杜松子酒（jsonb_array_lower（標籤））
“tmp_posts_lists_idx_user_id1532588309” btree (user_id)

按標籤獲取列表很快：

EXPLAIN ANALYSE
SELECT * FROM posts_lists
WHERE jsonb_array_lower(tags) ? lower('Qui');

Posts_lists 上的點陣圖堆掃描（成本=1397.50..33991.24 行=10000 寬度=56）（實際時間=0.110..0.132 行=2 循環=1）
重新檢查條件: (jsonb_array_lower(tags) ? 'qui'::text)
堆塊：精確=2
-> tmp_posts_lists_idx_tags1532588309 上的點陣圖索引掃描（成本=0.00..1395.00 行=10000 寬度=0）（實際時間=0.010..0.010 行=2 循環=1）
索引條件: (jsonb_array_lower(tags) ? 'qui'::text)
規劃時間：0.297 ms
執行時間：0.157 ms

獲取按分數排序的列表，限制 100 - 也很快：

EXPLAIN ANALYSE
SELECT *
FROM posts_lists
ORDER BY score_rank(score, id) DESC
LIMIT 100;

限制（成本=0.56..12.03 行=100 寬度=88）（實際時間=0.074..0.559 行=100 循環=1）
-> 在posts_lists上使用tmp_posts_lists_idx_score_desc1532588309進行索引掃描（成本=0.56..1146999.15行=10000473寬度=88）（實際時間=0.072..0.535行=100循環=1）
規劃時間：0.586 ms
執行時間：0.714 ms

但是結合以上兩個查詢非常慢：

EXPLAIN ANALYSE
SELECT * FROM posts_lists
WHERE jsonb_array_lower(tags) ? lower('Qui')
ORDER BY score_rank(score, id) DESC
LIMIT 100;

限制（成本=0.56..33724.60 行=100 寬度=88）（實際時間=2696.965..493476.142 行=2 循環=1）
-> 在posts_lists上使用tmp_posts_lists_idx_score_desc1532588309進行索引掃描（成本=0.56..3372404.39行=10000寬度=88）（實際時間=2696.964..493476.139行=2循環=1）
過濾器: (jsonb_array_lower(tags) ? 'qui'::text)
過濾器刪除的行數：9999998
規劃時間：0.426 ms
執行時間：493476.190 ms

為什麼？如何提高查詢效率？

上面用到的兩個函式的定義：

create or replace function score_rank(score integer, id text)
 returns text as $$
select case when score < 0
 then '0' || lpad((100000000 + score) :: text, 8, '0') || id
      else '1' || lpad(score :: text, 8, '0') || id
      end
$$
language sql immutable;


create or replace function jsonb_array_lower(arr jsonb)
 returns jsonb as $$
SELECT jsonb_agg(lower(elem))
FROM jsonb_array_elements_text(arr) elem
$$
language sql immutable;

排序和分頁

您的函式根據分數和附加的 PKscore_rank()生成 a 。這對排序沒有幫助。完全更換它，我懷疑你根本不需要它。而是使用兩列並直接進行排序：text``integer``id``score``id

SELECT *
FROM   posts_lists
ORDER  BY score DESC, id DESC
LIMIT  100;

tmp_posts_lists_idx_score_desc1532588309用更小、更快、維護成本更低、更通用的索引替換您的索引(score DESC, id DESC)。

您還可以通過行值比較有效地基於此多列索引進行分頁。看：

• 在大表上使用 OFFSET 優化查詢
• ‘WHERE (col1, col2) < (val1, val2)’ 的 SQL 語法術語

您後來提到了一個將字元串與 base256 等連接起來的新函式。所有這些聰明的技巧都不會提高性能。對 an 進行排序integer比在 Postgres 中對字元串進行排序要快。使用integer(or bigint) 而不是varchar(20)實際上會以多種方式提供幫助。

統計和查詢計劃（又名：為什麼？）

主要問題是缺少嵌套在jsonb列中的值的統計資訊。因此，Postgres有時會誤判謂詞的選擇性jsonb_array_lower(tags) ? lower('Qui')並選擇錯誤的查詢計劃。在您的範例LIMIT 2中，查詢計劃器的邏輯可以這樣說明 - 我們稱之為**“計劃 1”**：

只有兩行得分最高？posts_lists_idx_score_desc讓我們從最高分開始掃描索引。運氣好的話，我們很快就會得到結果！

對於大多數情況下，這是一個合理的計劃，至少有中度常見的標籤。但事實證明，“qui”這個標籤非常罕見，而且分數也很低。最壞的情況。Postgres 最終掃描了近 400 萬行，只為保留 2 行。這是對時間的巨大浪費：

Rows Removed by Filter: 3847383

如果查詢計劃者知道該標籤實際上有多罕見，它將從另一個索引開始，posts_lists_idx_tags就像我們在第二個範例中看到的那樣LIMIT 100- 我們稱之為**“計劃 2”**：

找到匹配的行，然後按分數排序，取前 N 個。

計劃 1更有利，標籤越小且LIMIT越頻繁。（如果符合條件的行恰好排在最前面。）

計劃 2越有利，標籤越大且LIMIT頻率越低。

Postgres 目前沒有關於文件類型（如jsonb. 而且根本沒有組合頻率。看：

• 當沒有結果並且指定了 LIMIT 時，SELECT 非常慢
• 具有 btree 索引的 jsonb 列的統計資訊不一致

更新：“組合統計”CREATE STATISTICS在 Postgres 10 中成為可能。

不管你做什麼，一定要執行最新版本的 Postgres。計劃者在每次發佈時都會變得更聰明。

備擇方案

1.一種想法可能是使用Postgres 數組( text[]) 和數組運算符而不是jsonb列來獲取最常見元素的**一些統計資訊。列, , 和在系統視圖中。most_common_elems``most_common_elem_freqs``elem_count_histogrampg_stats

幫助 Postgres 為某些星座生成更好的查詢計劃，但這不是靈丹妙藥。對於初學者，只儲存最常見的元素。Postgres 仍然不知道最稀有的元素。

2. 標準化您的數據庫設計並移動tags到一個單獨的1:n 表，每行一個標籤。這增加了磁碟佔用空間，因為每個標籤增加了行成本。posts_lists（但更改標籤變得更便宜，表膨脹更少。）如果您的標籤是穩定的，請考慮與新表之間的完整 n:m 關係tags。對於許多常見標籤來說，這也有點小。這是“乾淨”的方式。您擁有更詳細的統計資訊，並且應該會看到更少的錯誤查詢計劃。

3.由於Postgres 10有一個to_tsvector()處理json(b)值的變體。所以現在很容易創建一個文本搜尋索引並使用文本搜尋運算符。

指數：

CREATE INDEX posts_lists_idx_tags_fts ON posts_lists USING gin (to_tsvector('simple', tags));

詢問：

SELECT * FROM posts_lists
WHERE  to_tsvector('simple', tags) @@ to_tsquery('simple', 'qui') -- text search is case insensitive
ORDER  BY score DESC, id DESC
LIMIT  2;

一定要使用**simple**字典。您不想要大多數其他詞典中內置的詞幹提取。

文本搜尋功能產生小寫輸出，它的設計都是不區分大小寫的。無需像您原來的功能那樣進行處理jsonb_array_lower()。

**4.**在堅持使用jsonb索引的同時，嘗試更專業的jsonb_path_ops操作符類：

CREATE INDEX ON posts_lists USING gin (jsonb_array_lower(tags) jsonb_path_ops);

查詢：

WHERE  jsonb_array_lower(tags) @> '["qui"]'

手冊：

儘管jsonb_path_ops操作符類只支持使用@>操作符進行查詢，但它比預設操作符類具有顯著的性能優勢jsonb_ops。jsonb_path_ops索引通常比相同數據上的索引小得多，jsonb_ops並且搜尋的特異性更好，特別是當查詢包含在數據中頻繁出現的鍵時。因此，搜尋操作通常比使用預設運算符類執行得更好。

但我對你的具體情況不抱太大希望。

**5.**使用“顆粒狀”指標體系，並結合程序解決方案。看：

• 空間索引能否幫助“範圍-按-限制”查詢

db<>fiddle here - 進行了許多測試…

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