为什么 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)

问题

为什么Bitmap Index Scan on boundaries2不使用列status和isonlastsync？

Answer 1

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

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

我建议至少对boolean值进行部分索引(带有 WHERE 条件)，并删除这两列以使该索引更轻量级。

Answer 2

我不能告诉你为什么，但我可以帮助你优化查询。

不应使用多列 GIN 索引，而应仅在jsonb表达式上使用 GIN 索引，在其他列上使用 b 树索引。

列的顺序很重要：将相等条件下使用的 oned 放在第一位，在开头选择最有选择性。接下来，将具有必须选择性不等式或IN条件的列放在一起。对于以下列，顺序无关紧要，因为它们只会在索引扫描中充当筛选器。

确保索引缓存在 RAM 中。

我希望你这样会更快。

Answer 3

我认为你问自己错了问题。正如Lukasz已经回答的那样，PostgreSQL可能会发现检查索引中的所有列的效率低下。这里的问题是您的索引在磁盘上太大。

可能通过尝试使此SQL更快，您将尽可能多的列添加到索引中，但这适得其反。

诀窍是意识到PostgreSQL必须读取多少数据才能找到您的记录。如果索引包含太多数据，则必须读取大量数据。另外，请注意，低基数列不能很好地与 BTree 和常见索引类型配合使用;通常，您希望避免为它们编制索引。

若要使索引尽可能小并且快速执行查找，您必须找到具有更多基数的列，或者更好的是，该列将为查询返回较少的行。我的猜测是"ctcSortOrder"。这将是索引的第一列。

现在查看，在按第一列筛选后，哪列现在具有最多的基数或将筛选出大多数行。我不知道你的数据，但"来源"看起来是一个很好的候选者。

尽量避免 jsonb 搜索，除非它们是基数的主要来源，并将索引保留为 Btree。BTree快了好几倍。

就像卢卡斯建议的那样，查看部分索引。例如，添加"WHERE Status In ('active'，'Back on market')和 isonlastsync='true'"，因为这两个可能在您的所有搜索中都是通用的。

底线是，使用更简单、更小的索引比为所有列编制索引更快。列的顺序确实很重要。除非有充分的理由，否则坚持使用 BTree(在非 btree 兼容类型中有很多基数)。

如果您的表很大(>10M 行)，请考虑表分区，例如通过 ctcSortOrder。但我不认为这是你的情况。