indexing - PostgreSQL：多列索引（jsonb，整数）部分与@>和=条件一起使用

Question

设置

具有一jsonb列attributes和非唯一数字 ID的表campaignid：

  CREATE TABLE coupons (
    id integer NOT NULL,
    created timestamp with time zone DEFAULT now( ) NOT NULL,
    campaignid bigint NOT NULL,
    attributes jsonb NOT NULL
  );

该表将有多达 500M 行、任意键/值attributes和数百个不同的campaignid值。

表上存在两个索引：

CREATE INDEX campaignid_attrs_idx ON coupons
USING gin (campaignid,attributes);

CREATE INDEX campaignid_idx ON coupons
USING btree (campaignid, deleted);

我做了什么

我执行了查询：

SELECT COUNT(*)
FROM coupons
WHERE 
(campaignid = 97 AND
   attributes @> '{"CountryId": 3}');

预期成绩

我希望索引被充分campaignid_attrs_idx使用(campaignid,attributes)并且查询能够很快完成。

实际结果

该查询需要很长时间（约 40 秒）才能执行。

这是来自的输出explain (ANALYZE, COSTS)：

Aggregate  (cost=32337.78..32337.79 rows=1 width=8) (actual time=39726.410..39726.414 rows=1 loops=1)
  ->  Bitmap Heap Scan on coupons  (cost=30164.40..32332.44 rows=2136 width=0) (actual time=16893.439..39549.891 rows=1088478 loops=1)
"        Recheck Cond: ((attributes @> '{""CountryId"": 3}'::jsonb) AND (campaignid = 97))"
        Rows Removed by Index Recheck: 10531586
        Heap Blocks: exact=138344 lossy=583282
        ->  BitmapAnd  (cost=30164.40..30164.40 rows=2136 width=0) (actual time=16837.885..16837.887 rows=0 loops=1)
              ->  Bitmap Index Scan on coupons_campaignid_attrs_index  (cost=0.00..1465.15 rows=178954 width=0) (actual time=9872.279..9872.279 rows=81799565 loops=1)
"                    Index Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
              ->  Bitmap Index Scan on campaignid_idx  (cost=0.00..28697.93 rows=2135515 width=0) (actual time=6454.972..6454.972 rows=3088167 loops=1)
                    Index Cond: (campaignid = 97)
Planning Time: 0.175 ms
Execution Time: 39726.480 ms

结论

似乎索引campaignid_attrs_idx用于attributes @> '{"CountryId": 3}'返回约 80M 行的查询的第一部分，而该索引用于并行返回约 3M 行的子句campaignid_idx的第二部分。将两个部分的结果相交以得出满足这两个条件的集合。然后是位图堆扫描，验证结果集符合所需条件，大部分时间（16893.439..39549.891）WHEREcampaignid = 97

---

我的主要问题是，为什么不campaignid_attrs_idx用于过滤这两个条件？

---

---

编辑：我删除了第二个索引campaignid_attrs_idx以查看多列索引是否将用于这两个条件。奇怪的是我仍然看到索引扫描中使用的唯一条件之一。这是计划：

Aggregate  (cost=181951.27..181951.28 rows=1 width=8) (actual time=209633.017..209633.018 rows=1 loops=1)
  ->  Bitmap Heap Scan on coupons  (cost=1424.30..181945.81 rows=2183 width=0) (actual time=8938.605..209401.433 rows=1091580 loops=1)
"        Recheck Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
        Rows Removed by Index Recheck: 31487517
        Filter: (campaignid = 97)
        Rows Removed by Filter: 80674951
        Heap Blocks: exact=121875 lossy=5572599
        ->  Bitmap Index Scan on coupons_campaignid_attributes_idx  (cost=0.00..1423.75 rows=179434 width=0) (actual time=8908.682..8908.682 rows=81802589 loops=1)
"              Index Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
Planning Time: 6.885 ms
Execution Time: 209638.234 ms

标签： indexingpostgresql-11query-planner