如何加快查询索引设计的某个表?

Question

请原谅我对索引和数据库非常陌生。我有一个包含如下列的表:

date        timestamp without time zone,
date_num                         bigint,
value                  double precision,
name                               text,
market                             text,
type                               text,
UNIQUE(name,date_num)

这个表是:

1. 几乎没有更新，每天只更新一次，每个名字一个新行。
2. 有600 - 1000万行
3. 对于列name，有许多行，每一行代表一年中唯一的一天。例如，name'companyA'有1250行，每一行都有一个唯一的日期/date_num。
4. date_num是一年中某一天的毫秒时间戳，我们使用它进行搜索，有时我们使用date。

我们搜索的内容之一是"找到日期之间收入最高的名称";，这意味着对于name= &;公司a &;我们将计算:

revenue = (`value` of companyA in 14/2/2022 - `value` of companyA in 14/2/2021)

我们需要找到收益最高的50个names。

由于某些原因，这项任务需要13秒，而我看到其他人在1秒内完成。

1. 对于这种情况，哪些指标是合理的?
2. 如果我们需要找到/计算像这样的date/name/value的许多变化，哪些索引是好的?

这里有一个查询，需要查找某个type的所有names的收入。这个查询并不理想，因为它服务于许多类型的查询，并且最初包含用于更改每个查询的sql字符串的参数

WITH BS AS (
SELECT date_num, name, value,
first_value(value) over (PARTITION BY name ORDER BY date_num) as o,
first_value(value) over (PARTITION BY name ORDER BY date_num DESC) as c,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY date_num DESC) as rn
FROM historical
WHERE date_num >= 1609459200 AND date_num <= 1640995200 AND type = 'typeA'
)
SELECT name, date_num, CASE WHEN o=0 THEN null ELSE 100 * ( (c - o)/o ) END as out_return
FROM BS
WHERE BS.rn = 1
ORDER BY out_return  DESC NULLS LAST
LIMIT 50

PS—type列对于95%的表具有特定的值，而对于其他5%只有其他值。

Answer 1

为这个查询建立索引的最好方法是在(type, date_num)上建立索引。不要将时间戳存储为数字。