部分索引
使用部分索引甚至是一个部分多列索引会非常适合您的需求。但是,您提到的条件
不需要在一个月前的记录中搜索值
是不稳定的。部分索引只能与文字或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
,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
EXECUTE format('DROP INDEX IF EXISTS %I', _idx);
EXECUTE format('
CREATE OR REPLACE FUNCTION %I()
RETURNS timestamp AS
$x$
SELECT %L::timestamp
$x$ LANGUAGE SQL IMMUTABLE', _func, COALESCE(NEW.start_ts, '-infinity'));
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甚至会执行仅索引扫描。这样速度就再也快不了。
creation_date, value
上建立一个复合索引? 这将是最简单的索引之一(但不是最快的)。 - Ihor Romanchenkocreation_date
在复合索引中可以被 PostgreSQL 用来排除一个月之前的记录。索引不仅用于搜索字段,还对过滤和连接字段非常有用。 - Ihor Romanchenko(value, creation_date)
上进行设置。经验法则是:先等式 - 后范围。在dba.SE的相关答案中了解更多信息。 但是,该问题特别要求“尽快搜索”。 - Erwin Brandstetter