有沒有更快的方法來計算 JSONB 標籤？

我試圖在 Postgres 9.5 中從這個查詢中擠出更多的性能。我正在執行它超過 400,000 行。

在玩弄它時，我注意到這些CASE語句增加了相當多的查詢成本——如果我用簡單地將一些現有列相加來替換它們，它會減半執行時間。有沒有更有效的方法來計算這些總和？

SELECT sum("tag1"), sum("tag2"), sum("total_tags")
FROM (
   SELECT people.data->'recruiter_id' AS recruiter_id,
         (CASE WHEN people.data->'tags' ? 'tag1' THEN 1 END) AS "tag1",
         (CASE WHEN people.data->'tags' ? 'tag2' THEN 1 END) AS "tag2", 
         ((CASE WHEN people.data->'tags' ? 'tag1' THEN 1 ELSE 0 END) +
          (CASE WHEN people.data->'tags' ? 'tag2' THEN 1 ELSE 0 END)) AS total_tags
   FROM people WHERE people.data->'tags' ?| ARRAY['tag1','tag2'] ) AS target
GROUP BY recruiter_id

輸出EXPLAIN ANALYSE：

HashAggregate  (cost=1076.30..1078.22 rows=550 width=202) (actual time=7043.115..7043.208 rows=449 loops=1)
 Group Key: (people.data -> 'recruiter_id'::text)
 ->  Bitmap Heap Scan on people  (cost=12.85..1072.72 rows=550 width=202) (actual time=13.908..2619.878 rows=48492 loops=1)
       Recheck Cond: ((data -> 'tags'::text) ?| '{tag1,tag2}'::text[])
       Heap Blocks: exact=26114
       ->  Bitmap Index Scan on index_people_on_data_tags  (cost=0.00..12.82 rows=550 width=0) (actual time=9.219..9.219 rows=48493 loops=1)
             Index Cond: ((data -> 'tags'::text) ?| '{tag1,tag2}'::text[])
Planning time: 0.139 ms
Execution time: 7043.291 ms

執行：

x86_64-pc-linux-gnu 上的 PostgreSQL 9.5.5，由 gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2 編譯，64 位

內部查詢和外部查詢由應用程序的不同部分生成。是否可以在不重組的情況下進行優化？

總體而言，這應該更快更簡單：

SELECT *, tag1 + tag2 AS total_tags
FROM  (
  SELECT (data->>'recruiter_id')::int AS recruiter_id  -- cheaper to group by int
       , count(*) FILTER (WHERE data->'tags' ? 'tag1') AS tag1
       , count(*) FILTER (WHERE data->'tags' ? 'tag2') AS tag2
  FROM   people
  WHERE  data->'tags' ?| ARRAY['tag1','tag2']
  GROUP  BY 1
  ) target;

• 假設這recruiter_id是一個整數實體，按整數值分組比包含**整數值的jsonb對象更便宜。我還假設，無論如何，您寧願在結果中獲得整數值。

• 在子查詢中只計算一次，然後在外部添加總和的計數SELECT。

• FILTER對條件計數使用聚合子句：

  • 在單個 SELECT 語句中返回多個範圍的計數

• 如果您想要更短的語法，這可以獲得相同的結果和性能：

count(data->'tags' ? 'tag1' OR NULL) AS tag1

索引和缺失統計資訊jsonb

通常，索引是大表性能的決定因素。但由於您的查詢檢索了 400.000 行中的 48.493 行，即 > 12%，因此索引根本沒有幫助該查詢。

為什麼做出這個糟糕的決定？查詢計劃器沒有/對象內的值的統計資訊，並且必鬚根據通用選擇性估計選擇最佳查詢計劃。它希望在查詢實際找到 ~ 90 x 的時候找到 ( )。使用點陣圖索引掃描的查詢計劃是一個糟糕的決定。順序掃描會更快（根本不使用索引）。json``jsonbrows = 550****rows=48493

該索引可能仍然有助於不太頻繁的標籤（如果有的話），一個表達式索引data->'tags'應該是最好的。甚至可能是一個jsonb_path_ops索引，結合一個適應的查詢。更多的：

• 在 JSON 數組中查找元素的索引

然而，由於這個和其他原因，在使用通用標籤時，一個普通的 Postgres 數組或完全規範化的模式將遠遠超過你的jsonb對象的性能。

postgresql-performance 列表中的這個討論正是關於你的問題：

• https://www.postgresql.org/message-id/54C738E1.8080405@agliodbs.com

嘗試

SELECT count("tag1"), count("tag2"), count("tag1")+count("tag2")
FROM (
   SELECT people.data->'recruiter_id' AS recruiter_id,
         nullif(people.data->'tags' ? 'tag1',false) AS "tag1",
         nullif(people.data->'tags' ? 'tag2',false) AS "tag2"
   FROM people
   ) AS target
GROUP BY recruiter_id
HAVING count("tag1")+count("tag2") > 0

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