sql - 300000 行的 DISTINCT ON 慢

Question

我有一个名为 assets 的表。这是 ddl:

create table assets (
    id            bigint                                 primary key,
    name          varchar(255)                           not null,
    value         double precision                       not null,
    business_time timestamp with time zone,
    insert_time   timestamp with time zone default now() not null
);

create index idx_assets_name on assets (name);

我需要为每个 Assets 名称提取最新的(基于 insert_time)值。这是我最初使用的查询:

SELECT DISTINCT
    ON (a.name) *
FROM home.assets a
WHERE a.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'
    )
ORDER BY a.name,
         a.insert_time DESC;

Assets 表中有大约 300,000 行。在我的 VPS 上，这个查询大约需要 800 毫秒。这导致特定端点的整个响应时间约为 1 秒。这有点慢，考虑到 Assets 表增长很快，这个端点在不久的将来会更慢。我还尝试避免 IN(...) 使用此查询:

SELECT DISTINCT
    ON (a.name) *
FROM home.assets a
ORDER BY a.name,
         a.insert_time DESC;

但我没有注意到显着差异。知道如何优化此查询吗？

Answer 1

您可以尝试将以下索引添加到您的表中:

CREATE INDEX idx ON assets (name, insert_time DESC);

如果使用，Postgres 可以简单地扫描这个索引来找到每个名称具有最近 insert_time 的不同记录。