30万行数据下PostgreSQL DISTINCT ON查询优化方案咨询

针对你的需求（提取每个资产名称的最新记录）和性能问题，以下是几个实用的优化方法：

1. 创建匹配排序需求的复合索引 #

原索引idx_assets_name仅包含name字段，但你的查询需要按name分组后再按insert_time DESC排序以获取最新行。创建复合索引可以让PostgreSQL直接利用索引定位目标数据，避免全表扫描或额外排序操作：

CREATE INDEX idx_assets_name_insert_time ON home.assets (name, insert_time DESC);

这个索引完全匹配ORDER BY name, insert_time DESC的逻辑，DISTINCT ON (name)可以直接通过索引快速提取每个name对应的第一条（最新）记录，大幅减少查询耗时。

2. 用窗口函数替代DISTINCT ON #

窗口函数ROW_NUMBER()在分组取最新值的场景中性能表现稳定，配合上面的复合索引效果更佳：

SELECT id, name, value, business_time, insert_time
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY insert_time DESC) AS rn
    FROM home.assets
    WHERE name IN (
        'USD_RLS', 'EUR_RLS', 'SEKKEH_RLS', 'NIM_SEKKEH_RLS',
        'ROB_SEKKEH_RLS', 'BAHAR_RLS', 'GOLD_18_RLS', 'GOLD_OUNCE_USD',
        'SILVER_OUNCE_USD', 'PLATINUM_OUNCE_USD', 'GOLD_MESGHAL_RLS',
        'GOLD_24_RLS', 'STOCK_IR', 'AED_RLS', 'GBP_RLS', 'CAD_RLS',
        'CHF_RLS', 'TRY_RLS', 'AUD_RLS', 'JPY_RLS', 'CNY_RLS',
        'RUB_RLS', 'BTC_USD'
    )
) t
WHERE rn = 1;

窗口函数会先按name分组，每组内按insert_time倒序编号，取编号为1的行即为最新记录。复合索引同样能加速这个分组排序过程。

3. 维护最新值汇总表（长期最优方案） #

如果表数据持续快速增长，每次查询都扫描历史数据会越来越慢。可以创建一个汇总表专门存储每个资产的最新记录，通过UPSERT或触发器自动维护：

步骤1：创建汇总表 #

CREATE TABLE home.assets_latest (
    name varchar(255) PRIMARY KEY,
    id bigint NOT NULL,
    value double precision NOT NULL,
    business_time timestamp with time zone,
    insert_time timestamp with time zone NOT NULL
);

步骤2：同步数据到汇总表 #

每次向assets插入新记录时，用UPSERT更新汇总表（如果该资产已存在则覆盖最新值，不存在则插入）：

-- 插入新资产记录时执行此语句
INSERT INTO home.assets_latest (name, id, value, business_time, insert_time)
VALUES ('[资产名称]', [ID值], [资产价值], [业务时间], [插入时间])
ON CONFLICT (name) DO UPDATE SET
    id = EXCLUDED.id,
    value = EXCLUDED.value,
    business_time = EXCLUDED.business_time,
    insert_time = EXCLUDED.insert_time;

也可以给assets表创建AFTER INSERT触发器，自动触发上述UPSERT操作，无需手动维护：

CREATE OR REPLACE FUNCTION sync_assets_latest()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO home.assets_latest (name, id, value, business_time, insert_time)
    VALUES (NEW.name, NEW.id, NEW.value, NEW.business_time, NEW.insert_time)
    ON CONFLICT (name) DO UPDATE SET
        id = EXCLUDED.id,
        value = EXCLUDED.value,
        business_time = EXCLUDED.business_time,
        insert_time = EXCLUDED.insert_time;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_sync_assets_latest
AFTER INSERT ON home.assets
FOR EACH ROW EXECUTE FUNCTION sync_assets_latest();

查询汇总表 #

之后查询最新记录只需直接访问汇总表，响应时间可降至毫秒级：

SELECT * FROM home.assets_latest
WHERE name IN (
    'USD_RLS', 'EUR_RLS', 'SEKKEH_RLS', 'NIM_SEKKEH_RLS',
    'ROB_SEKKEH_RLS', 'BAHAR_RLS', 'GOLD_18_RLS', 'GOLD_OUNCE_USD',
    'SILVER_OUNCE_USD', 'PLATINUM_OUNCE_USD', 'GOLD_MESGHAL_RLS',
    'GOLD_24_RLS', 'STOCK_IR', 'AED_RLS', 'GBP_RLS', 'CAD_RLS',
    'CHF_RLS', 'TRY_RLS', 'AUD_RLS', 'JPY_RLS', 'CNY_RLS',
    'RUB_RLS', 'BTC_USD'
);

4. 辅助优化：更新统计信息 #

如果PostgreSQL的统计信息过时，可能会生成低效的执行计划。运行以下命令更新表统计信息：

ANALYZE home.assets;

内容的提问来源于stack exchange，提问作者MehdiB