优化大表中最近行的查询性能

Question

我有一张很大的表格：

CREATE TABLE "orders" (
"id" serial NOT NULL,
"person_id" int4,
"created" int4,
CONSTRAINT "orders_pkey" PRIMARY KEY ("id")
);

所有请求中有90%是涉及最近2-3天内一个person_id的订单，例如：

select * from orders
where person_id = 1
and created >= extract(epoch from current_timestamp)::int - 60 * 60 * 24 * 3;

如何提高性能？

我知道有关分区（Partitioning）的知识，但对于现有行呢？而且好像每2-3天都需要手动创建INHERITS表。

- user2024300

---

你了解Indexing吗？ - Nick Barnes

是的，当然，我已经有了索引（person_id, created）。 - user2024300

只在 created 上创建一个索引有什么问题吗？我不明白分区如何对此有所帮助。这只会成为一个低效的索引。因此，最好一开始就创建索引。 - usr

Answer 1

一个在(person_id, created)上使用伪IMMUTABLE条件的部分、多列索引将会极大地帮助性能（需要定期重新创建以保持性能）。

请注意，如果您的表不是很大，您可以大大简化并使用普通的多列索引。
或者考虑在Postgres 12或更高版本中使用表分区（该功能终于成熟了）。

一个原始的函数提供了一个常量时间点，向前3天或以上（在您的情况下表示为Unix纪元）：

CREATE OR REPLACE FUNCTION f_orders_idx_start()
  RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE COST 1 AS
'SELECT 1387497600';

PARALLEL SAFE 仅适用于 Postgres 10 或更高版本。
1387497600 是以下结果：

SELECT extract(epoch from now())::integer - 259200;
-- 259200 being the result of 60 * 60 * 24 * 3

基于此伪IMMUTABLE条件建立你的部分索引：

CREATE INDEX orders_created_recent_idx ON orders (person_id, created)
WHERE created >= f_orders_idx_start();

基于相同条件进行查询：

SELECT *
FROM   orders
WHERE  person_id = 1
AND    created >= f_orders_idx_start()  -- match partial idx condition
AND    created >= extract(epoch from now())::integer - 259200;  -- actual condition

这行代码AND created >= f_orders_idx_start()看起来有些多余，但它对于说服Postgres使用部分索引至关重要。

定期重新创建函数和索引的函数。可能每晚都会使用cron-job执行：

CREATE OR REPLACE FUNCTION f_orders_reindex_partial()
  RETURNS void AS
$func$
DECLARE
   -- 3 days back, starting at 00:00
   _start int := extract(epoch from now()::date -3)::int;
BEGIN       
   IF _start = f_orders_idx_start() THEN
      -- do nothing, nothing changes.
   ELSE
      DROP INDEX IF EXISTS orders_created_recent_idx;
      -- Recreate IMMUTABLE function
      EXECUTE format('
         CREATE OR REPLACE FUNCTION f_orders_idx_start()
           RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE COST 1 AS
         $$SELECT %s $$'
       , _start
      );
      -- Recreate partial index
      CREATE INDEX orders_created_recent_idx ON orders (person_id, created)
      WHERE created >= f_orders_idx_start();
   END IF;    
END
$func$  LANGUAGE plpgsql;

然后，为了重新设置你的索引，请调用（最好没有或很少的并发负载）：

SELECT f_orders_reindex_partial();  -- that's all

如果由于并发负载无法承受索引的删除和重建，请考虑在Postgres 12或更高版本中使用REINDEX CONCURRENTLY。它非常简单：

REINDEX INDEX orders_created_recent_idx;

所有查询都可以正常工作，即使您从未调用此函数。随着部分索引的增长，性能会逐渐下降。

我已经成功地在几个大表和类似要求的情况下使用了这种方案。非常快。

对于Postgres 9.2或更高版本，并且如果您的表只有少量小列，并且表没有被频繁写入，可能值得将其作为覆盖索引：

CREATE INDEX orders_created_recent_idx ON orders (person_id, created<b>, id</b>)
WHERE created >= f_orders_idx_start();

在Postgres 11或更高版本中，您可能希望使用INCLUDE代替：

CREATE INDEX orders_created_recent_idx ON orders (person_id, created) <b>INCLUDE (id)</b>
WHERE created >= f_orders_idx_start();

- Erwin Brandstetter

Answer 2

建议：-

这可能对你有所帮助。由于表的大小不断增长，你的查询性能会逐渐下降。最好保留3-5天（如果你非常确定只需要访问2-3天）的记录，并定期将旧记录迁移到备份表中。

- Pavunkumar

---

是的，谢谢。我考虑过这个选项，但它需要做出相当多的更改，因为10%的请求必须进入备份表。 - user2024300

我认为有比手动拆分表格然后每2-5天手动或通过脚本维护它的更简单的方法。 - user2024300

如果您不依赖于旧记录，那么您可以拥有具有相同结构的备份表，并使用db_link轻松迁移数据。然后将其作为批处理（自动化）迁移操作。 - Pavunkumar