sql - 从PostgreSQL搜索中排除过时数据的最佳方法

我有一个包含以下各列的表:

一个名为id的整数列

名为value的文本列

名为creation_date的时间戳列

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

我必须在该表中搜索给定的值，并希望尽可能快地进行搜索。但是我真的不需要仔细查看超过一个月的记录。因此，理想情况下，我想将它们从索引中排除。

什么是实现这一目标的最佳方法:

执行表分区。仅搜索适当月份的子表。

创建仅包含最近记录的部分索引。每月重新创建一次。

还有别的吗？

(PS:“最佳解决方案”是指最方便，快速且易于维护的解决方案)

最佳答案

偏指数
为此，partial index甚至是部分multicolumn index都是完美的。但是你的情况

不稳定。部分索引的条件只能与文字或IMMUTABLE函数(即常量值)一起使用。您提到了Recreate it every month，但这与您的定义older than one month不一致。您看到其中的区别了吗？
如果您只需要当前(或上一个)月，则索引重新创建以及查询本身将变得更加简单!
对于此答案的其余部分，我将使用您的定义“不超过一个月” 。我以前不得不处理过这样的情况。以下解决方案最适合我:
将索引条件基于固定的时间戳记，并在查询中使用相同的时间戳记，以说服查询计划程序可以使用部分索引。这种部分将在较长的时间内保持有用，只是随着添加新行而旧行退出您的时间范围，其有效性才会降低。索引将返回越来越多的误报，其他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位于第一位。 this related answer on 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注入(inject)以及繁重的并发负载下的锁定问题!
此“指导表”在您的管理体制中为每个表接收一行:

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行的小测试用例更新了 fiddle ，以演示它的工作原理。
PostgreSQL甚至会为我的示例查询执行仅索引扫描。不会比这更快。

关于sql - 从PostgreSQL搜索中排除过时数据的最佳方法，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16168504/