postgresql - 高效检索大型表 PostgreSQL 中的最新值

Question

目前，在努力获得一种有效的方法来查询以下格式的表格后，我正在使用此查询...

select distinct on (symbol, date) date, symbol, value, created_time
from "test_table"
where symbol in ('symbol15', 'symbol19', 'symbol36', 'symbol54', 'symbol13', 'symbol90', 'symbol115', 'symbol145', 'symbol165', 'symbol12')
order by symbol, date, created_time desc

有了这个索引...

test_table(symbol, date, created_time)

下面是一个数据示例，显示了我正在使用的列。真正的表是 1300 万行。

date           symbol      value      created_time
2010-01-09     symbol1     101        3847474847
2010-01-10     symbol1     102        3847474847
2010-01-10     symbol1     102.5      3847475500
2010-01-10     symbol2     204        3847474847
2010-01-11     symbol1     109        3847474847
2010-01-12     symbol1     105        3847474847
2010-01-12     symbol2     206        3847474847

目前，看起来 80% 以上的查询都花在了基于 EXPLAIN ANALYZE 的排序上。知道如何提高此查询的速度吗？我需要为每个日期和符号组合获取最新的 created_time。

Answer 1

由于您的 where 子句仅使用列 symbol，因此不会使用您创建的索引。

我建议你在 symbol 上创建一个索引:

CREATE INDEX ON test_table(symbol);

此外，这可能是编写查询的更好方式

SELECT date, symbol, MAX(created_time)
FROM "test_table"
WHERE symbol in ('symbol15', 'symbol19', 'symbol36', 'symbol54', 'symbol13', 'symbol90', 'symbol115', 'symbol145', 'symbol165', 'symbol12')
GROUP BY date, symbol
ORDER BY symbol, date
LIMIT 10;

如果可以的话，添加限制将大大提高性能。

您应该运行 EXPLAIN ANALYZE SELECT... 以更好地了解使用或未使用哪些索引以及 PostgreSQL 如何运行您的查询。