一个在(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;
基于此伪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()
AND created >= extract(epoch from now())::integer - 259200;
这行代码
AND created >= f_orders_idx_start()
看起来有些多余,但它对于说服Postgres使用部分索引至关重要。
定期重新创建函数和索引的
函数。可能每晚都会使用cron-job执行:
CREATE OR REPLACE FUNCTION f_orders_reindex_partial()
RETURNS void AS
$func$
DECLARE
_start int := extract(epoch from now()::date -3)::int;
BEGIN
IF _start = f_orders_idx_start() THEN
ELSE
DROP INDEX IF EXISTS orders_created_recent_idx;
EXECUTE format('
CREATE OR REPLACE FUNCTION f_orders_idx_start()
RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE COST 1 AS
$$SELECT %s $$'
, _start
);
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();
如果由于并发负载无法承受索引的删除和重建,请考虑在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();
created
上创建一个索引有什么问题吗?我不明白分区如何对此有所帮助。这只会成为一个低效的索引。因此,最好一开始就创建索引。 - usr