将日期时间约束添加到PostgreSQL多列部分索引
我有一个名为queries_query的PostgreSQL表,它有很多列.
其中两个创建的列和user_sid经常在我的应用程序的SQL查询中一起使用,以确定给定用户在过去30天内完成了多少查询.在最近30天之前的任何时间查询这些统计数据是非常非常罕见的. 这是我的问题: 我目前通过运行以下方法在这两列上创建了我的多列索引: CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid,created) 但我想进一步限制索引只关心创建日期在过去30天内的查询.我尝试过以下方法: CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid,created) WHERE created >= NOW() - '30 days'::INTERVAL` 但这引发了一个异常,说明我的函数必须是不可变的. 我很乐意让这个工作,以便我可以优化我的索引,并削减Postgres需要执行这些重复查询的资源. 解决方法
在尝试使用now()时会出现异常,因为该函数不是IMMUTABLE(显然),我引用
the manual here:
我在这里看到了两种利用(效率更高)部分索引的方法: 1.使用常数日期的条件的部分索引: CREATE INDEX queries_recent_idx ON queries_query (user_sid,created) WHERE created > '2013-01-07 00:00'::timestamp; 假设created实际上定义为timestamp.为timestamptz列(带时区的时间戳)提供时间戳常量是不行的.从时间戳到timestamptz的转换(反之亦然)取决于当前时区设置,并且不是不可变的.使用匹配数据类型的常量.了解带/不带时区的时间戳的基础知识: > Ignoring timezones altogether in Rails and PostgreSQL 在流量较低的小时内删除并重新创建该索引,可能每天或每周都有一个cron作业(或者对你来说足够好).创建索引非常快,尤其是部分索引相对较小.此解决方案也不需要向表中添加任何内容. 假设没有对表的并发访问,可以使用如下函数完成自动索引重新创建: CREATE OR REPLACE FUNCTION f_index_recreate() RETURNS void AS $func$ BEGIN DROP INDEX IF EXISTS queries_recent_idx; EXECUTE format(' CREATE INDEX queries_recent_idx ON queries_query (user_sid,created) WHERE created > %L::timestamp',LOCALTIMESTAMP - interval '30 days'); -- timestamp constant --,now() - interval '30 days'); -- alternative for timestamptz END $func$ LANGUAGE plpgsql; 呼叫: SELECT f_index_recreate(); now()(和你一样)相当于CURRENT_TIMESTAMP并返回timestamptz.使用now():: timestamp转换为时间戳或使用LOCALTIMESTAMP代替. > Select today’s (since midnight) timestamps only 用Postgres 9.2 – 9.4测试. 如果必须处理并发访问,请使用CREATE INDEX CONCURRENTLY.但是你不能将这个命令包装成一个函数,因为,per documentation:
因此,有两个单独的交易: CREATE INDEX CONCURRENTLY queries_recent_idx2 ON queries_query (user_sid,created) WHERE created > '2013-01-07 00:00'::timestamp; -- your new condition 然后: DROP INDEX CONCURRENTLY IF EXISTS queries_recent_idx; (可选)重命名为旧名称: ALTER INDEX queries_recent_idx2 RENAME TO queries_recent_idx; 2.具有“存档”标签条件的部分索引 向表中添加已归档的代码: ALTER queries_query ADD COLUMN archived boolean NOT NULL DEFAULT FALSE; 以您选择的间隔更新列以“退出”旧行并创建如下索引: CREATE INDEX some_index_name ON queries_query (user_sid,created) WHERE NOT archived; 为查询添加匹配条件(即使看起来多余),以允许它使用索引.使用EXPLAIN ANALYZE检查查询规划器是否捕获 – 它应该能够在较新的日期使用索引进行查询.但它不会理解更复杂的条件不完全匹配. 您不必删除并重新创建索引,但表上的UPDATE可能比索引重新创建更昂贵,并且表格略大. 我会选择第一个选项(索引娱乐).事实上,我在几个数据库中使用此解决方案.第二个会导致更高成本的更新. 随着时间的推移,两种解决方案都保持其有用性,随着索引中包含更多过时的行,性能会逐渐恶化. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |