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

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;

如何提高性能？

我知道分区，但是现有行呢？看来我需要INHERITS每2-3天手动创建表格。

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使用部分索引。

一个 函数来重建功能和指标 不时。可能每天晚上都做一次正式工作：

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

如果由于并发负载而无法删除和重新创建索引，请考虑REINDEX CONCURRENTLY使用Postgres 12或更高版本。简直太简单了：

REINDEX INDEX orders_created_recent_idx;

即使您从未调用此函数，所有查询仍将继续工作。 随着部分索引的增加，性能会随着时间的推移而缓慢下降。

我已经成功地将这种html" target="_blank">机制与几个大型表和类似的需求结合使用了。 非常快。

对于Postgres 9.2或更高版本，并且如果您的表只有很少的小列，并且该表的写入量不大，则可能需要花一个
覆盖索引 ：

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

在Postgres
11或更高版本中，您可能要使用INCLUDE：

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