如何让PostgreSQL使用函数索引优化分组统计查询

表结构与索引 #

CREATE TABLE items
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('items_sequence') PRIMARY KEY,
    item_price NUMERIC(19, 2) DEFAULT NULL NULL,
    status NUMERIC(2, 0) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

已创建的函数索引：

CREATE INDEX items_dash_idx ON items (status, DATE(created_at));

需求 #

按状态（仅统计10、20、30、40，排除数据量极大的状态50）和日期分组统计近30天数据，需包含记录数/金额为0的情况。

原查询及性能问题 #

原查询SQL：

SELECT COUNT(i.id)                    AS count,
       COALESCE(SUM(i.item_price), 0) AS amount,
       dates_table.status,
       dates_table.created_at
FROM (SELECT created_at::DATE AS created_at, 10 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
      UNION
      SELECT created_at::DATE AS created_at, 20 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
      UNION
      SELECT created_at::DATE AS created_at, 30 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
      UNION
      SELECT created_at::DATE AS created_at, 40 AS status
      FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') AS created_at
     ) AS dates_table
LEFT JOIN items i 
       ON i.status = dates_table.status
      AND DATE(i.created_at) = dates_table.created_at
GROUP BY dates_table.created_at, dates_table.status
ORDER BY dates_table.created_at, dates_table.status;

该查询耗时超10秒，执行计划显示对items表做了全表扫描，未使用items_dash_idx索引，且缓存命中率从99.9%降至50%。但单独查询单个状态时能正常使用索引，先过滤items表再关联仅需约100ms。

方案1：先过滤聚合items表，再关联维度表 #

先对items表中需要的状态和日期范围做过滤，利用索引快速获取数据并聚合，再和生成的日期-状态维度表左连接，强制触发索引使用：

WITH filtered_items AS (
    SELECT 
        status,
        DATE(created_at) AS created_date,
        COUNT(id) AS count,
        COALESCE(SUM(item_price), 0) AS amount
    FROM items
    WHERE 
        status IN (10, 20, 30, 40)
        AND DATE(created_at) BETWEEN '2024-09-18'::DATE AND '2024-10-18'::DATE
    GROUP BY status, DATE(created_at)
),
dates_table AS (
    SELECT 
        gs.created_at::DATE AS created_at,
        s.status
    FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') gs(created_at)
    CROSS JOIN (VALUES (10), (20), (30), (40)) s(status)
)
SELECT 
    COALESCE(fi.count, 0) AS count,
    COALESCE(fi.amount, 0) AS amount,
    dt.status,
    dt.created_at
FROM dates_table dt
LEFT JOIN filtered_items fi 
    ON dt.status = fi.status 
    AND dt.created_at = fi.created_date
ORDER BY dt.created_at, dt.status;

索引触发逻辑：WHERE子句中的status IN (...)和DATE(created_at) BETWEEN ...完全匹配索引(status, DATE(created_at))的前缀和第二列，PostgreSQL会直接使用该索引定位符合条件的行，避免全表扫描。

方案2：显式过滤items表的状态 #

在左连接时通过子查询过滤items表的状态，减少扫描数据量，触发索引：

SELECT 
    COUNT(i.id) AS count,
    COALESCE(SUM(i.item_price), 0) AS amount,
    dt.status,
    dt.created_at
FROM (
    SELECT 
        gs.created_at::DATE AS created_at,
        s.status
    FROM GENERATE_SERIES('2024-09-18'::DATE, '2024-10-18'::DATE, INTERVAL '1 DAY') gs(created_at)
    CROSS JOIN (VALUES (10), (20), (30), (40)) s(status)
) dt
LEFT JOIN (
    SELECT id, item_price, status, DATE(created_at) AS created_date
    FROM items
    WHERE status IN (10, 20, 30, 40)
) i ON dt.status = i.status AND dt.created_at = i.created_date
GROUP BY dt.created_at, dt.status
ORDER BY dt.created_at, dt.status;

索引触发逻辑：子查询中的WHERE status IN (...)会触发索引扫描，只取出需要的状态数据，避免扫描状态50的大量冗余数据。

方案3：升级为覆盖索引（可选） #

如果聚合操作需要频繁访问id和item_price，可以将这两个字段加入索引，形成覆盖索引，避免回表查询：

CREATE INDEX items_dash_covering_idx ON items (status, DATE(created_at)) INCLUDE (id, item_price);

这样聚合时直接从索引中获取所有所需数据，无需访问表的主数据块，进一步提升性能。

内容的提问来源于stack exchange，提问作者Hasan Can Saral