如何在Postgres中使用WHERE子句添加索引

Question

我想在Postgres中添加一个带有WHERE子句的索引。我使用了以下查询来实现这个目标：

create index concurrently em_openorder_idx on line (m_product_id, org_id, date) where date >= now() - 90

但是我遇到了以下错误：

functions in index predicate must be marked IMMUTABLE

- Kathirvel Appusamy

---

您无法索引最近90天的数据--这就是错误的含义。请将所有数据都进行索引。 - Gordon Linoff

你能详细说明一下你想做什么吗？看起来像是一个XY问题。 - Juan Carlos Oropeza

行是一个大表，所以我想用日期范围创建索引。当我们创建索引时，它将具有仅包含90天记录的虚拟表。因此查询速度将更快。@JuanCarlosOropeza - Kathirvel Appusamy

错误异常被引发，因为now()不是一个不可变函数，它在所有方面都是易变的。您不能在任何索引中使用易变函数。 - LongBeard_Boldy

@LongBeard_Boldy：它很稳定；与clock_timestamp()不同，now()在事务中永远不会改变。虽然在这里并没有太大的区别 :) - Nick Barnes

不要试图欺骗分区，直接进行分区。将line重命名为line_archive。创建一个具有相同结构的新行表，并使其成为line_archive的父表。创建一个从line继承的line_current表。创建一个每晚运行的进程，将超过90天的数据从line_current移动到line_archive。所有新插入的数据都需要进入line_current，但可以通过对line表进行before触发器来隐藏代码。更新和删除将继续工作。您仍然需要在两个表上建立日期索引，但查询将很快。 - Jim Nasby

Answer 1

WHERE子句中的表达式必须是不可变的，即对于给定的参数集，每次调用它时必须返回相同的值。 now() 显然不符合要求。

您可以使用以下方式索引最近90天的数据：

create index concurrently em_openorder_idx on line (m_product_id,org_id,date) 
where date>='now'::date-90

然而，如果您回头查看索引定义，您会发现它已被转换为一个常数表达式：

... WHERE date >= ('2016-03-02'::date - 90);

换句话说，这个90天的时间窗口不会随着时间自动向前移动；您需要定期删除和重新创建此索引。

另一个需要注意的是，只有当查询使用不可变表达式比较日期时，才能使用此索引。例如，在这里将使用索引：

SELECT * FROM line WHERE date = '2016-03-02';

...但不能在此处使用：

SELECT * FROM line WHERE date = CURRENT_DATE;

---

作为旁注，如果您使用的是Postgres 9.5，这个表可能是BRIN索引的一个好选择。

- Nick Barnes

---

我对最后的陈述提出异议，current_date将在特定查询中使用索引。 - LongBeard_Boldy

@LongBeard_Boldy：不会的；看这个SQLFiddle。 - Nick Barnes

抱歉，我的错。是的，如果你使用部分索引，在WHERE子句中使用非不可变函数的查询将无法使用索引。但是，如果你对整个列创建索引，规划器会使用该索引。 - LongBeard_Boldy

Answer 2

只需创建索引。

create index concurrently em_openorder_idx on line (m_product_id,org_id,date)

我猜你想实现类似于这样的查询。

EXPLAIN ANALYZE
SELECT *
FROM line
WHERE m_product_id = @id
  AND date>=now()-90

这将使用索引，速度应该非常快。

- Juan Carlos Oropeza

---

这里，它会从完整的表中进行选择，而我并不希望这样，因为表非常大。 - Kathirvel Appusamy

不会的，索引会为你过滤行。如果像我刚刚编辑的那样添加 EXPLAIN ANALYZE，你可以在查询计划中看到索引如何过滤数据。 - Juan Carlos Oropeza

日期字段是时间戳还是日期字段？在where子句中，您可以使用current_date函数代替now()。 - LongBeard_Boldy