优化时间戳范围的Postgres查询 [英] Optimize Postgres query on timestamp range

问题描述

我定义了下表和索引：

CREATE TABLE ticket (
  wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),
  eid bigint,
  created timestamp with time zone NOT NULL DEFAULT now(),
  status integer NOT NULL DEFAULT 0,
  argsxml text,
  moduleid character varying(255),
  source_id bigint,
  file_type_id bigint,
  file_name character varying(255),
  status_reason character varying(255),
  ...
)

我在created时间戳上创建了索引，如下所示：

CREATE INDEX ticket_1_idx
  ON ticket
  USING btree
  (created );

以下是我的问题：

select * from ticket 
where created between '2012-12-19 00:00:00' and  '2012-12-20 00:00:00'

在记录数开始增长(约为500万)之前，这一直运行良好，但现在需要花费很长时间才能返回。

解释分析显示：

Index Scan using ticket_1_idx on ticket  (cost=0.00..10202.64 rows=52543 width=1297) (actual time=0.109..125.704 rows=53340 loops=1)
  Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with time zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time zone))
Total runtime: 175.853 ms

到目前为止，我已尝试设置：

random_page_cost = 1.75 
effective_cache_size = 3 

还创建：

create CLUSTER ticket USING ticket_1_idx;

所有内容都不起作用。我做错了什么？为什么选择顺序扫描？索引应该使查询速度更快。有什么可以做的来优化它吗？

推荐答案

CLUSTER

如果您打算使用CLUSTER，则显示的语法无效。

<罢工>create CLUSTER ticket USING ticket_1_idx;

运行一次：

CLUSTER ticket USING ticket_1_idx;

此可以对更大的结果集有很大帮助。对于返回的单行或几行则不是很多。
Postgres记住要为后续调用使用哪个索引。如果您的表不是只读的，效果会随着时间的推移而恶化，您需要以一定的间隔重新运行：

CLUSTER ticket;

可能仅限于易失性分区。请参见下面的内容。

但是如果您有大量更新，CLUSTER(或VACUUM FULL)实际上可能会影响性能。适量的膨胀允许UPDATE将新的行版本放在同一数据页上，并且避免了过于频繁地物理扩展底层物理文件的需要。您可以使用经过仔细调整的FILLFACTOR来两全其美：

• Fill factor for a sequential index that is PK

pg_repack/pg_squeeze

CLUSTER对表进行独占锁定，这在多用户环境中可能是一个问题。Quoting the manual:

聚集表时，将获取ACCESS EXCLUSIVE锁 这就去办。这会阻止任何其他数据库操作(读取和 写入)，直到CLUSTER完成。

强调我的。考虑一下替代方案！

pg_repack：

与CLUSTER和VACUUM FULL不同，它可以在线工作，不需要持有 在处理期间对已处理的表进行独占锁定。PG_REPACK为 引导效率高，性能与直接使用CLUSTER相当。

和：

pg_repack需要在重组结束时获得独占锁。

当前版本1.4.7支持PostgreSQL 9.4-14。

pg_squeeze是一个较新的替代方案，它声称：

实际上，我们尝试替换pg_repack扩展。

当前版本1.4适用于Postgres 10-14。

查询

查询足够简单，本身不会导致任何性能问题。

但是，关于正确性：BETWEEN构造包括边界。您的查询将选择12月19日的所有加上记录，时间为12月20日00：00小时。这是极不可能的要求。很有可能，您真的想要：

SELECT *
FROM   ticket 
WHERE  created >= '2012-12-19 0:0'
AND    created <  '2012-12-20 0:0';

性能

首先，您可以问：

它为什么选择顺序扫描？

您的EXPLAIN输出清楚地显示索引扫描，而不是顺序表扫描。一定是有什么误会。

您可能能够提高性能，但问题不涉及必要的背景信息。可能的选项包括：

• 仅查询必填列，而不是*，以降低转移成本(和其他性能优势)。

• 查看partitioning并将实际时间片放入单独的表中。根据需要向分区添加索引。

• 如果没有分区选项，则另一种相关但侵入性较小的技术将是添加一个或多个partial indexes。
  例如，如果您主要查询当月，则可以创建以下部分索引：

    CREATE INDEX ticket_created_idx ON ticket(created)
    WHERE created >= '2012-12-01 00:00:00'::timestamp;
  

  CREATE新索引就在新月开始之前。您可以使用cron作业轻松地自动执行任务。 可选的DROP旧月份的部分索引。

  为CLUSTER保留总索引(不能对部分索引进行操作)。如果旧记录从不更改，则表分区将对此任务有很大帮助，因为您只需要重新群集较新的分区。
  不过，如果记录根本不更改，您可能不需要CLUSTER。

性能基础知识

您可能缺少其中一个基本要素。所有常见的性能建议均适用：

• https://wiki.postgresql.org/wiki/Slow_Query_Questions
• https://wiki.postgresql.org/wiki/Performance_Optimization

这篇关于优化时间戳范围的Postgres查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！