当前位置: 首页 > 面试题库 >

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

支嘉祥
2023-03-14
问题内容

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

  • 一个名为的整数列 id
  • 一个名为的文本列 value
  • 一个名为timestamp的列 creation_date

当前,已经为idvalue列创建了索引。

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

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

  1. 执行表分区。仅在子表中搜索适当的月份。
  2. 创建仅包含最近记录的部分索引。每月重新创建一次。
  3. 还有别的吗

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


问题答案:

偏指数

一个 局部索引
将是最理想的选择,或者甚至是一个局部多列索引。但是你的情况

不需要搜索超过一个月的记录中的值

不是稳定的 。部分索引的条件只能与文字或IMMUTABLE函数(即常量值)一起使用。您提到了Recreate it every month,但这与您的定义不一致older than one month。您看到其中的区别了吗?

如果您只需要当前(或上一个)月,则索引重新创建以及查询本身将变得更加简单!

对于该答案的其余部分,我将为您定义 “不超过一个月” 。我以前不得不处理过这样的情况。以下解决方案最适合我:

将索引条件基于固定的时间戳记,并在查询中使用相同的时间戳记,以说服查询计划程序可以使用部分索引。这种部分将在较长时间内保持有用,但随着添加新行而旧行退出您的时间范围,只会降低其有效性。索引将返回越来越多的误报,其他WHERE子句必须从您的查询中消除这些误报。重新创建索引以更新其条件。

给定您的测试表:

CREATE TABLE mytbl (
   value text
  ,creation_date timestamp
);

创建一个非常简单的IMMUTABLESQL函数:

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

@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甚至 会对 我的示例查询 进行仅索引扫描 。不会比这更快。



 类似资料:
  • 问题内容: 我有一个600万行的SQLite表。 从表中删除是很慢的。 删除表然后重新创建它似乎更快。 我正在使用它进行数据库导入。 删除表会是更好的方法还是有办法快速删除所有数据? 问题答案: 一个很大的不同是DML和DDL。对于数据库事务,这非常重要。最后的结果可能是相同的,但是这些操作有很大的不同。 如果您只是要了解性能,那么可以删除并重新创建表。如果您需要在导入中进行事务处理,则必须注意,

  • 我最近在一次面试中进行了一次编码测试。我被告知: 有一个100万int的大型未排序数组。用户想要检索K个最大的元素。您将实现什么算法? 在这期间,有人强烈暗示我需要对数组进行排序。 所以,如果性能真的很重要,我建议使用内置的或自定义实现。然后我被告知,使用或数组来存储最大和for循环,可以实现大约,事后看来,我认为这是,因为每次迭代都需要与大小的数组进行比较以找到要替换的最小元素,而对数组进行排序

  • 如何从以后的每次搜索中排除目录中的文件,而无需手动键入排除项。

  • 当执行整个Rails应用程序的搜索时,我使用自定义范围来排除我不关心的区域的结果。比如日志文件、供应商插件等。这一直运行良好,除了自从添加了RVM支持以来,我的搜索现在包括安装的每个gem中的所有文件。当我的项目代码中实际上只有几个命中时,这通常会产生大量的搜索结果。 我很难想出一个规则来排除我的 .rvm 目录中的 gem。 我已经尝试了大量的排除规则的变体,如下所示: 我怀疑问题可能与项目外部

  • 我有一个二进制搜索树,它的每个节点都有两个值。 所以它的节点是这样的。 我已经根据节点的“name”变量的升序在BST中插入了值。所以树的顺序遍历将按“name”的升序返回节点。 现在我想根据“值”变量的升序显示树节点。无需更改原始树。哪种算法/方法对此最有效?

  • 我有一个包含一些价格值的数据帧。不,我想有一个或最好的情况下有两个数据框,每篇文章的最大值和最小值没有 0 个值。 我用DT这样做(对于maxValue,一切都很完美): 但minValue Df显示0值。我也尝试过: 但是在这里我不知道如何使用 在最好的情况下,我希望每个产品的最大值和最小值都有dfs。