postgresql – 优化Postgres时间戳查询范围

发布时间：2020-12-13 16:15:52 所属栏目：百科来源：网络整理

导读：我有以下表和索引定义： 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 characte

我有以下表和索引定义：

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),...
)

我在创建的时间戳上创建了一个索引,如下所示：

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,则显示的语法无效.

使用ticket_1_idx创建CLUSTER票证;

运行一次：

CLUSTER ticket USING ticket_1_idx;

对于更大的结果集,这可以帮助很多.返回单行并非如此.
Postgres会记住用于后续调用的索引.如果您的表格不是只读的,则效果会随着时间的推移而恶化,您需要以一定的间隔重新运行：

CLUSTER ticket;

可能只在易失性分区上.见下文.

但是,如果你有很多更新,CLUSTER(或VACUUM FULL)可能实际上对性能有害.适量的膨胀允许UPDATE在同一数据页面上放置新的行版本,并且避免了太频繁地在OS中物理扩展底层文件的需要.您可以使用经过精心调整的FILLFACTOR来充分利用这两个方面：

> Fill factor for a sequential index that is PK

pg_repack

CLUSTER对表进行独占锁定,这可能是多用户环境中的问题. Quoting the manual:

When a table is being clustered,an ACCESS EXCLUSIVE lock is acquired
on it. This prevents any other database operations (both reads and
writes) from operating on the table until the CLUSTER is finished.

大胆强调我的.考虑alternative pg_repack：

Unlike CLUSTER and VACUUM FULL it works online,without holding an
exclusive lock on the processed tables during processing. pg_repack is
efficient to boot,with performance comparable to using CLUSTER directly.

和：

pg_repack needs to take an exclusive lock at the end of the reorganization.

版本1.3.1适用于：

PostgreSQL 8.3,8.4,9.0,9.1,9.2,9.3,9.4

版本1.4.2适用于：

PostgreSQL 9.1,9.4,9.5,9.6,10

询问

查询很简单,不会导致任何性能问题.

但是,关于正确性的说法：BETWEEN构造包括边界.您的查询将选择12月19日的所有内容,以及12月20日00:00时的记录.这是一个非常不可能的要求.机会是,你真的想要：

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

性能

首先,你问：

Why is it selecting sequential scan?

您的EXPLAIN输出清楚地显示了索引扫描,而不是顺序表扫描.必定存在某种误解.

如果你为了更好的表现而努力,你可以改善一些事情.但是必要的背景信息不在问题中.可能的选择包括：

>您只能查询所需的列而不是*来降低传输成本(以及可能的其他性能优势).
>您可以查看partitioning并将实际时间片放入单独的表中.根据需要向分区添加索引.
>如果分区不是一个选项,另一个相关但较少侵入性的技术是添加一个或多个partial indexes.
例如,如果您主要查询当前月份,则可以创建以下部分索引：

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

在新月开始之前创建新索引.您可以使用cron作业轻松自动执行任务.
(可选)DROP部分索引在过去几个月之后.
>另外保留CLUSTER的总索引(不能对部分索引进行操作).如果旧记录永远不会更改,表分区将对此任务有很大帮助,因为您只需要重新集群较新的分区.
然后,如果记录永远不会改变,您可能不需要CLUSTER.

如果你结合最后两个步骤,性能应该是很棒的.

性能基础知识

您可能遗漏了其中一个基础知识.所有通常的性能建议都适用：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!