Postgresql

當查詢有許多（超過 18 個）OR 語句時忽略 GIN 索引

  • September 2, 2019

我有一個問題，即執行超過 18 個 OR 語句時未使用 GIN 索引。

這是我的索引：

CREATE INDEX emailjobs_externalreferences_gin
ON vsko_mailer_api_prod.emailjobs USING gin
("externalReferences" jsonb_path_ops)
TABLESPACE pg_default;

這是查詢：

explain analyze
select * from "emailjobs"
where "emailjobs"."$$meta.deleted" = false
 and ("externalReferences" @> '[{"href":"/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)
order by "$$meta.created" asc,"key" asc
limit '500'

結果：

"Limit  (cost=0.08..8857.81 rows=500 width=1380) (actual time=766.232..1182.058 rows=49 loops=1)"
"  ->  Index Scan using emailjobs_ordered_created_and_keys on emailjobs  (cost=0.08..70064.73 rows=3955 width=1380) (actual time=766.230..1182.049 rows=49 loops=1)"
"        Filter: ((NOT "$$meta.deleted") AND (("externalReferences" @> '[{"href": "/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)))"
"        Rows Removed by Filter: 216447"
"Planning time: 0.237 ms"
"Execution time: 1182.107 ms"

所以它沒有使用 GIN 索引。

然而，我一直在尋找轉折點。如果我有 18 個或更少的 OR 語句，它似乎使用 GIN 就好了。

explain analyze
select * from "emailjobs"
where "emailjobs"."$$meta.deleted" = false
 and ("externalReferences" @> '[{"href":"/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb
  OR  "externalReferences" @> '[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)
order by "$$meta.created" asc,"key" asc
limit '500'

結果：

"Limit  (cost=8938.34..8938.59 rows=500 width=1380) (actual time=23.443..23.455 rows=49 loops=1)"
"  ->  Sort  (cost=8938.34..8940.21 rows=3749 width=1380) (actual time=23.442..23.448 rows=49 loops=1)"
"        Sort Key: "$$meta.created", key"
"        Sort Method: quicksort  Memory: 123kB"
"        ->  Bitmap Heap Scan on emailjobs  (cost=2673.05..8900.97 rows=3749 width=1380) (actual time=23.000..23.326 rows=49 loops=1)"
"              Recheck Cond: (("externalReferences" @> '[{"href": "/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb) OR ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb))"
"              Filter: (NOT "$$meta.deleted")"
"              Heap Blocks: exact=62"
"              ->  BitmapOr  (cost=2673.05..2673.05 rows=3781 width=0) (actual time=22.922..22.922 rows=0 loops=1)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.283..1.283 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.281..1.281 rows=2 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.358..1.358 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/0f93b24c-ee8f-4872-896c-9a75fcb904e0"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.196..1.196 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/10f38c74-6b6f-4298-819f-a2a2e61f23ce"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.249..1.249 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/127468cf-0bb4-41fe-8d5d-2886a4f0d4a4"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.312..1.312 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/297aefb9-e4aa-4b9e-9dcd-8687df1b200a"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.291..1.291 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2e5c4b39-4375-49be-9bee-7b8106502c59"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.247..1.247 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2f08f86c-696b-4f71-bbe3-51cf2bc74387"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.284..1.284 rows=3 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2f443528-b173-4522-bdb2-7c942112d781"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.261..1.261 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/2f8e87f6-4573-478d-925d-fca286cc6cee"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.268..1.268 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/318417fa-9c41-4377-b815-02f40147ce65"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.296..1.296 rows=3 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/3b4cd61c-6adc-4b56-8519-5bd2612fb214"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.223..1.223 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/3e5ed89e-bfa4-47f9-a056-9817f8e91a4a"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.273..1.273 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/404a90d6-a6e8-4068-8d0c-904685705129"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.287..1.287 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/524a5dde-7245-4796-9cd5-1f1eee9b4f6a"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.240..1.240 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/5b4b6606-e3ec-49a4-8882-0faeb7d51f60"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.255..1.255 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/62e267f7-6720-4874-8c24-270465d7f171"}]'::jsonb)"
"                    ->  Bitmap Index Scan on emailjobs_externalreferences_gin  (cost=0.00..148.32 rows=210 width=0) (actual time=1.310..1.310 rows=5 loops=1)"
"                          Index Cond: ("externalReferences" @> '[{"href": "/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"}]'::jsonb)"
"Planning time: 0.249 ms"
"Execution time: 23.623 ms"

知道如何優化這個查詢，允許更多的 OR 語句（至少像 50 個？）並且仍然使用/索引？

這是 externalReferences 數據的範例：

"[{"href":"/responsibilities/pending/64212e18-ba9e-4dc6-b35b-9bd66ec4d49a"},{"href":"/sam/organisationalunits/4fc33e81-6a08-4be8-82b9-8822f77787d5"}]"

編輯：

externalReferences 也可以是一個空數組。

問題是 PostgreSQL 假設當您指定更多OR條件時將返回更多行，因此在某些時候它會認為索引掃描不會再快了。

您可以創建一個函式來提取 JSON 中有趣的部分：

CREATE FUNCTION get_array(jsonb) RETURNS text[]
  LANGUAGE sql IMMUTABLE AS
$$SELECT array_agg(x->>'href') FROM jsonb_array_elements($1) AS v(x)$$;

然後你可以索引

CREATE INDEX ON vsko_mailer_api_prod.emailjobs USING gin (get_array("externalReferences"));

並像這樣查詢：

... WHERE get_array("externalReferences")
         && ARRAY['/responsibilities/pending/06ad562d-379a-4827-a9d3-1de179e29852',
                  '/responsibilities/pending/0e754454-db34-4ec2-8192-898f34928e80',
                  ...
                 ]

“重疊”運算符應該做你想做的事。

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

相關問答

Postgresql

Postgres 不使用表達式索引

  • March 1, 2018
檢視問答
Postgresql

PostgreSQL 是否將帶有 OR 的 JOIN 重寫為 UNION？

  • October 9, 2017
檢視問答
Postgresql

大表的高效分頁

  • January 30, 2022
檢視問答
Postgresql

PostgreSQL：WHERE 子句中的 ANY (VALUES(…)) 導致速度急劇下降

  • January 20, 2022
檢視問答
Postgresql

PostgreSQL - 多列 B-Tree 索引如何與第一列的 order by 和第二列的 IN 查找一起工作？

  • January 13, 2022
檢視問答
Postgresql

我的大表的 Postgresql 查詢非常慢

  • November 29, 2021
檢視問答