sql - 优化对大表的最近行的查询性能

Question

我有一张大 table :

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 表。

Answer 1

A partial, multicolumn index在 (person_id, created) 上使用伪 IMMUTABLE 条件会有所帮助(很多)。需要不时重新创建以保持性能。

请注意，如果您的表不是很大，您可以在很大程度上简化并使用普通的多列索引。
或者考虑 table partitioning在 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;

然后，要 rebase 您的索引，请调用(理想情况下并发负载很少或没有并发负载):

SELECT f_orders_reindex_partial();  -- that's all

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

REINDEX INDEX orders_created_recent_idx;

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

我成功地使用了这个机制来满足几个大表和类似的要求。 非常快。

对于 Postgres 9.2 或更高版本，如果您的表只有很少的小列，并且表的内容不多，那么将其设为 covering index 可能是值得的:

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();