postgresql - 为什么 postresql 不使用多列索引中的所有列？

Question

我正在使用扩展程序

CREATE EXTENSION btree_gin;

我有一个看起来像这样的索引......

create index boundaries2 on rets USING GIN(source, isonlastsync, status, (geoinfo::jsonb->'boundaries'), ctcvalidto, searchablePrice, ctcSortOrder);

在我开始弄乱它之前，索引看起来像这样，结果与我将要分享的结果相同，因此索引定义中的微小变化似乎没有什么不同:

create index boundaries on rets USING GIN((geoinfo::jsonb->'boundaries'), source, status, isonlastsync, ctcvalidto, searchablePrice, ctcSortOrder);

我给 pgsql 11 这个查询:

explain analyze select id from rets where ((geoinfo::jsonb->'boundaries' ?| array['High School: Torrey Pines']) AND source='SDMLS'
          AND searchablePrice>=800000 AND searchablePrice<=1200000 AND YrBlt>=2000 AND EstSF>=2300
         AND Beds>=3 AND FB>=2 AND ctcSortOrder>'2019-07-05 16:02:54 UTC' AND Status IN ('ACTIVE','BACK ON MARKET')
         AND ctcvalidto='9999-12-31 23:59:59 UTC' AND isonlastsync='true') order by LstDate desc, ctcSortOrder desc LIMIT 3000;

结果...

 Limit  (cost=120.06..120.06 rows=1 width=23) (actual time=472.849..472.850 rows=1 loops=1)
   ->  Sort  (cost=120.06..120.06 rows=1 width=23) (actual time=472.847..472.848 rows=1 loops=1)
         Sort Key: lstdate DESC, ctcsortorder DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on rets  (cost=116.00..120.05 rows=1 width=23) (actual time=472.748..472.841 rows=1 loops=1)
               Recheck Cond: ((source = 'SDMLS'::text) AND (((geoinfo)::jsonb -> 'boundaries'::text) ?| '{"High School: Torrey Pines"}'::text[]) AND (ctcvalidto = '9999-12-31 23:59:59+00'::timestamp with time zone) AND (searchableprice >= 800000) AND (searchableprice <= 1200000) AND (ctcsortorder > '2019-07-05 16:02:54+00'::timestamp with time zone))
               Rows Removed by Index Recheck: 93
               Filter: (isonlastsync AND (yrblt >= 2000) AND (estsf >= 2300) AND (beds >= 3) AND (fb >= 2) AND (status = ANY ('{ACTIVE,"BACK ON MARKET"}'::text[])))
               Rows Removed by Filter: 10
               Heap Blocks: exact=102
               ->  Bitmap Index Scan on boundaries2  (cost=0.00..116.00 rows=1 width=0) (actual time=471.762..471.762 rows=104 loops=1)
                     Index Cond: ((source = 'SDMLS'::text) AND (((geoinfo)::jsonb -> 'boundaries'::text) ?| '{"High School: Torrey Pines"}'::text[]) AND (ctcvalidto = '9999-12-31 23:59:59+00'::timestamp with time zone) AND (searchableprice >= 800000) AND (searchableprice <= 1200000) AND (ctcsortorder > '2019-07-05 16:02:54+00'::timestamp with time zone))
 Planning Time: 0.333 ms
 Execution Time: 474.311 ms
(14 rows)

问题

为什么列 status 和 isonlastsync 没有被 Bitmap Index Scan on boundaries2 使用？

Answer 1

如果它预测过滤掉这些列会更快，它就可以这样做。如果列的基数非常低并且您将获取所有行的足够大的部分，通常就是这种情况；这对于像 isonlastsync 这样的 boolean 是正确的，通常对于只有几个不同值的状态列也是如此。

Rows Removed by Filter: 10 要过滤掉的行数很少，要么是因为您的表不包含大量行，要么是因为其中大部分符合您为这两列指定的条件。您可以尝试在该表中生成更多数据或选择状态稀有的行。

我建议做 partial indexes (使用 WHERE 条件)，至少对于 boolean 值并删除这两列以使该索引更轻量级。