在PostgreSQL中排除过时数据的最佳方法

Question

在PostgreSQL中排除过时数据的最佳方法

sqlpostgresqlindexingplpgsqlddl

5

我有一个包含以下列的表格：

一个名为id的整数列
一个名为value的文本列
一个名为creation_date的时间戳列

目前，已经为id和value列创建了索引。

我必须搜索这个表格以查找给定的值，并希望尽可能地快速搜索。但是我真的不需要查看一个月之前的记录。因此，理想情况下，我希望将它们从索引中排除。

如何实现最佳方案呢？

对表进行分区。仅在适当的月份子表中搜索。
创建包含最近记录的部分索引。每个月重新创建一次。
其他方法？

（注： “最佳解决方案”指的是最方便、快速且易于维护的解决方案）

- boqapt

也许在 creation_date, value 上建立一个复合索引？这将是最简单的索引之一（但不是最快的）。 - Ihor Romanchenko

为什么要将creation_date包含在索引中？我搜索的是值，而不是创建日期。 - boqapt

creation_date 在复合索引中可以被 PostgreSQL 用来排除一个月之前的记录。索引不仅用于搜索字段，还对过滤和连接字段非常有用。 - Ihor Romanchenko

@IgorRomanchenko：如果您将其设置为复合索引（对于小到中型表足够好），请在(value, creation_date)上进行设置。经验法则是：先等式 - 后范围。在dba.SE的相关答案中了解更多信息。但是，该问题特别要求“尽快搜索”。 - Erwin Brandstetter

@ErwinBrandstetter 是的，我把复合索引字段的顺序写错了。至于问题 - 它要求“最方便、简单和快速”的方法（问题的最后一句话）。+我已经提到，复合索引不是最快的方法。 - Ihor Romanchenko

@IgorRomanchenko：嗯，你说得有道理，一个简单的多列索引肯定更简单、更方便创建。部分索引会让它更快。即使你只创建一次并且从不更新。但仔细想想，最好的解决方案是使用部分多列索引来处理错误的匹配。我会更新我的答案。 - Erwin Brandstetter

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

部分索引

使用部分索引甚至是一个部分多列索引会非常适合您的需求。但是，您提到的条件

不需要在一个月前的记录中搜索值

是不稳定的。部分索引只能与文字或IMMUTABLE函数一起使用，即常量值。您提到了每个月重新创建，但这与您的定义一个月前并不相符。您看出区别了吗？

如果您仅需要当前（或上个）月份，则索引重建和查询本身都会变得更加简单！

我将使用您的定义“不超过一个月”来回答剩余的问题。我以前也遇到过类似的情况。对我而言，以下解决方案效果最好：

基于固定时间戳设置索引条件，并在查询中使用相同的时间戳来确保查询规划器可以使用部分索引。这种部分索引在长时间内仍然有效，只有随着加入新行和较旧的行退出您的时间范围，其效果会逐渐降低。索引将返回越来越多的错误结果，需要使用额外的WHERE子句从查询中排除它们。重新创建索引以更新其条件。

考虑您的测试表：

CREATE TABLE mytbl (
   value text
  ,creation_date timestamp
);

创建一个非常简单的IMMUTABLE SQL函数：

CREATE OR REPLACE FUNCTION f_mytbl_start_ts()
  RETURNS timestamp AS
$func$
SELECT '2013-01-01 0:0'::timestamp
$func$ LANGUAGE sql IMMUTABLE;

在部分索引的条件中使用该函数：

CREATE INDEX mytbl_start_ts_idx ON mytbl(value, creation_date)
WHERE (creation_date >= f_mytbl_start_ts());

value 首先要考虑。在 dba.SE 相关答案中有解释。
评论区的 @Igor 提供了一个不错的建议，部分多列索引可以使得从部分索引中排除错误结果更快——因为索引条件的本质是始终过时的（但仍然比没有好很多）。

查询

像这样的查询将会利用到索引并且非常快速：

SELECT value
FROM   mytbl
WHERE  creation_date >= f_mytbl_start_ts()            -- !
AND    creation_date >= (now() - interval '1 month')
AND    value = 'foo';

唯一目的看似多余的WHERE子句：creation_date >= f_mytbl_start_ts()是为了使查询规划器使用部分索引。

您可以手动删除和重新创建函数和索引。

完全自动化

或者，您可以在可能有许多类似表的更大方案中自动化它：

免责声明：这是高级内容。您需要知道自己在做什么，并考虑用户权限、可能的SQL注入和并发负载下的锁定问题！

这个“控制表”每行接收一个表：

CREATE TABLE idx_control (
   tbl text primary key  -- plain, legal table names!
  ,start_ts timestamp
);

我建议将所有这类元对象放在一个单独的模式中。

对于我们的示例：

INSERT INTO idx_control(tbl, value)
VALUES ('mytbl', '2013-1-1 0:0');

一个“导航表”可以让您在一个中心位置方便地查看所有这些表以及它们各自的设置，并且您可以同步更新一些或所有表。如果您更改此表中的start_ts，则会触发以下触发器来处理其余部分：

触发器函数：

CREATE OR REPLACE FUNCTION trg_idx_control_upaft()
  RETURNS trigger AS
$func$
DECLARE
   _idx  text := NEW.tbl || 'start_ts_idx';
   _func text := 'f_' || NEW.tbl || '_start_ts';
BEGIN

-- Drop old idx
EXECUTE format('DROP INDEX IF EXISTS %I', _idx);

-- Create / change function; Keep placeholder with -infinity for NULL timestamp
EXECUTE format('
CREATE OR REPLACE FUNCTION %I()
  RETURNS timestamp AS
$x$
SELECT %L::timestamp
$x$ LANGUAGE SQL IMMUTABLE', _func, COALESCE(NEW.start_ts, '-infinity'));

-- New Index; NULL timestamp removes idx condition:    
IF NEW.start_ts IS NULL THEN 
   EXECUTE format('
   CREATE INDEX  %I ON %I (value, creation_date)', _idx, NEW.tbl);
ELSE
   EXECUTE format('
   CREATE INDEX  %I ON %I (value, creation_date)
   WHERE  creation_date >= %I()', _idx, NEW.tbl, _func);
END IF;

RETURN NULL;

END
$func$ LANGUAGE plpgsql;

触发器：

CREATE TRIGGER upaft
AFTER UPDATE ON idx_control
FOR EACH ROW
WHEN (OLD.start_ts IS DISTINCT FROM NEW.start_ts)
EXECUTE PROCEDURE trg_idx_control_upaft();

现在，简单的 UPDATE 在转向表上校准索引和功能：

UPDATE idx_control
SET    start_ts = '2013-03-22 0:0'
WHERE  tbl = 'mytbl';

您可以运行cron作业或手动调用此操作。
使用索引的查询不会更改。

-> SQLfiddle。
我更新了这个例子，使用了10k行数据来展示这个方法的工作原理。对于我的示例查询，PostgreSQL甚至会执行仅索引扫描。这样速度就再也快不了。