Postgresql – Multicolumn GIN index not fully facilitated by query planner

execution-planpostgresqlpostgresql-11postgresql-performancequery-performance

I am experiencing unexpected behaviour with ProstgreSQL 11.10 query planner with multicolumn GIN index usage.

After defining an index on two columns (a,b) of a large table and executing a query involving conditions for both columns a = 4 and b @> '{"key": "value"}'::jsonb, the planner applies the multicolumn index only for one of the two conditions. It seems that another scan is executed in parallel for the other condition, using another index. Why can this happen?

A few more details:

The index is:

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

The query is:

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

The plan is:

Aggregate  (cost=34532.02..34532.03 rows=1 width=8) (actual time=56613.161..56613.164 rows=1 loops=1)
  ->  Bitmap Heap Scan on coupons  (cost=32208.05..34526.31 rows=2284 width=0) (actual time=23541.180..56384.603 rows=1090012 loops=1)
"        Recheck Cond: ((attributes @> '{""CountryId"": 3}'::jsonb) AND (campaignid = 97))"
        Rows Removed by Index Recheck: 10532325
        Heap Blocks: exact=138539 lossy=583394
        ->  BitmapAnd  (cost=32208.05..32208.05 rows=2284 width=0) (actual time=23478.234..23478.236 rows=0 loops=1)
              ->  Bitmap Index Scan on coupons_campaignid_attrs_index  (cost=0.00..1543.35 rows=179379 width=0) (actual time=14247.668..14247.668 rows=81803572 loops=1)
"                    Index Cond: (attributes @> '{""CountryId"": 3}'::jsonb)"
              ->  Bitmap Index Scan on campaignid_deleted_idx  (cost=0.00..30663.31 rows=2284099 width=0) (actual time=8658.669..8658.670 rows=3097095 loops=1)
                    Index Cond: (campaignid = 97)
Planning Time: 1.711 ms
Execution Time: 56619.880 ms

EDIT: I removed the second index campaignid_attrs_idx to see if then the multicolumn index will be used for both conditions. Strangely I still see that the only one of the conditions used in the index scan. Here's the plan:

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

Best Answer

I cannot reproduce this issue where it chooses to use two indexes, but I suspect it has something to do with the massively incorrect estimate for JSON containment condition; combined with another unknown factor like maybe some kind of index bloat.

...rows=179434 width=0) (actual time=8908.682..8908.682 rows=81802589

That is off by over 400 fold. But there isn't much you can do about it, other than pull "CountryId" out into its own column, or upgrade to v13 where json containment selectivity estimation has been improved (though not by as much as if it were in its own column).

In any event, the choice of two different indexes is unlikely to actually be a problem. When you use a GIN multicolumn index like this, it behaves pretty much the same as two different indexes combined with a BitmapAnd. It is just that the BitmapAnd is occurring inside a black box where EXPLAIN can't show you the details. I would generally not expect much difference in performance. (One place I have seen a difference is if the tuples which match one condition are physically separated from the ones which match the other, i.e. all the tuples with campaignid = 97 are physically before all the tuples with attributes @> '{"CountryId": 3}' in the table. The opaque BitmapAnd of the gin indexes has a optimization for this case that the transparent BitmapAnd does not seem to have.)

If your main goal is to make the query faster rather than either micromanaging exactly how to sublists get combined or redesigning your table, then I see two things to try. One is increasing work_mem, as the massive number of lossy blocks can't be good for performance. The other would be to create a multicolumn btree index on (campaignid, ((attributes->>'CountryId')::int)); and then rewrite the query accordingly:

WHERE campaignid = 97 AND (attributes->> 'CountryId')::integer=3
Related Question