PostgreSQL 多列索引未完全使用

Question

我在 PostgreSQL 12.3 上有一个大(约 1.1 亿行)表，其相关字段可以用以下 DDL 描述:

CREATE TABLE tbl
(
    item1_id integer,
    item2_id integer,
    item3_id integer,
    item4_id integer,
    type_id integer
)

我们经常执行的查询之一是:

SELECT type_id, item1_id, item2_id, item3_id, item4_id
FROM tbl
WHERE
    type_id IS NOT NULL
    AND item1_id IN (1, 2, 3)
    AND (
        item2_id IN (4, 5, 6)
        OR item2_id IS NULL
    )
    AND (
        item3_id IN (7, 8, 9)
        OR item3_id IS NULL
    )
    AND (
        item4_id IN (10, 11, 12)
        OR item4_id IS NULL
    )

尽管我们为每个单独的列建立了索引，但查询仍然相对较慢(几秒钟)。为了优化这一点，我创建了以下索引:

CREATE INDEX tbl_item_ids
    ON public.tbl USING btree
    (item1_id ASC, item2_id ASC, item3_id ASC, item4_id ASC)
    WHERE type_id IS NOT NULL;

不幸的是，查询性能几乎没有提高 - EXPLAIN告诉我这是因为虽然对这个新创建的索引进行了索引扫描，但只有 item1_id用作 Index Cond ，而所有其他过滤器都应用于表级别(即普通 Filter )。
我不确定为什么没有完全使用索引(或至少不超过 item1_id 列)。这有明显的原因吗？有没有办法可以重构索引或查询本身以帮助提高性能？

Answer 1

如果第一列上的条件使用相等比较 ( = )，则多列索引只能用于多于第一列。 IN或 = ANY不符合条件。
因此，您最好为每列使用单独的索引，这些索引可以与位图或。
你应该尽量避免 OR在 WHERE条件，也许与

WHERE coalesce(item2_id, -1) IN (-1, 4, 5, 6)

其中 -1 是一个不会出现的值。然后你可以在 coalesce 上使用索引表达。