不用于联接的索引

Question

我加入了listings_tail表，并对整数类型的listing_id列进行了物化视图cals_reviewed_wide_scaled。

该表有大约15亿行和2百万个惟一的清单ids。MV的测试范围为500至500 000行。这两列都用btree、散列和brin索引。

尽管有可用的索引，explain始终返回Seq Scan。

如何优化索引以使其工作？

查询

SELECT * from listings_tail L
INNER JOIN cals_reviewed_wide_scaled R 
ON L.listing_id = R.listing_id;

表

\d listings_tail

 id                   | bigint                      | not null default nextval('listings_tail_id_seq'::regclass)
 listing_id           | integer                     | not null
 parsing_timestamp    | timestamp without time zone | not null
 value                | character varying           | not null
 homes_details_inside | integer                     | not null
 tags                 | character varying[]         | not null
Indexes:
    "listings_tail_pkey" PRIMARY KEY, btree (id)
    "listings_tail_dd_unique" UNIQUE CONSTRAINT, btree (listing_id, value, homes_details_inside, tags)
    "listing_id_tail" btree (listing_id)
    "tail_listing_id_brin" brin (listing_id)
    "tail_listing_id_hash" hash (listing_id)

\d cals_reviewed_wide
 id                | integer                     | not null default nextval('cals_reviewed_wide_id_seq'::regclass)
 listing_id        | integer                     | not null
 date              | timestamp without time zone | not null
 price             | double precision            |
 parsing_timestamp | timestamp without time zone | not null
 min_nights        | double precision            |
 max_nights        | double precision            |
 available         | integer                     | not null
 created_at        | timestamp without time zone | not null
 revs_id           | integer                     | not null
Indexes:
    "cals_reviewed_wide_pkey" PRIMARY KEY, btree (id)
    "cals_reviewed_wide_unique" UNIQUE CONSTRAINT, btree (listing_id, date)
    "cals_reviewed_wide_revs_id_ixs" btree (revs_id)
    "ix_cals_reviewed_wide_listing_id" btree (listing_id)
Foreign-key constraints:
    "cals_reviewed_wide_revs_id_fkey" FOREIGN KEY (revs_id) REFERENCES reviews(id)

\d random_cals_reviewed_wide_scaled

View "public.random_cals_reviewed_wide_scaled"
 Column  |  Type  | Modifiers
---------+--------+-----------
 revs_id | bigint |

\d cals_reviewed_wide_scaled
Materialized view "public.cals_reviewed_wide_scaled"

-------------------+-----------------------------+-----------
 listing_id        | integer                     |
 date              | timestamp without time zone |
 price             | double precision            |
 parsing_timestamp | timestamp without time zone |
 min_nights        | double precision            |
 max_nights        | double precision            |
 created_at        | timestamp without time zone |
 revs_id           | integer                     |
Indexes:
    "cals_reviewed_wide_scaled_listing_id_brin" brin (listing_id)
    "cals_reviewed_wide_scaled_listing_id_hash" hash (listing_id)
    "cals_reviewed_wide_scaled_listing_id_ixs" btree (listing_id)

物化视图

CREATE MATERIALIZED VIEW cals_reviewed_wide_scaled
      as 
      SELECT 
       C.listing_id, 
       C.date, 
       C.price,
       C.parsing_timestamp,
       C.min_nights,
       C.max_nights,
       C.created_at,
       C.revs_id
       FROM cals_reviewed_wide as C
       JOIN random_cals_reviewed_wide_scaled as V
       ON C.revs_id = V.revs_id;

解释

EXPLAIN (ANALYZE, BUFFERS)：

Answer 1

这两个表都不小，并且没有限制两个表中行数的WHERE条件。

work_mem足够大，足以包含cals_reviewed_wide_scaled的哈希，所以最有效的计划是哈希连接。

使用散列连接、不能使用任何索引。和顺序扫描是最有效的访问方法。

如果嵌套的循环连接可能更快，您可以尝试。

为此，请确保listings_tail.listing_id上有索引，然后尝试以下操作以推动PostgreSQL朝着正确的方向前进：

• 尝试将effective_cache_size增加到所拥有的内存数量。
• 尝试将random_page_cost降低到1.0。
• 如果全部失败，尝试在数据库会话中设置以下内容: SET enable_hashjoin = off；SET enable_mergejoin = off；然后再试一次，您将看到嵌套的循环连接。这样，您就可以测试这是否确实更快，而且PostgreSQL是错误的。