在时间戳范围内优化Postgres查询

Question

我有以下表格和索引定义：

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万条）之前，这个东西还运行良好，现在返回结果需要很长时间。

使用Explain analyze可以得出以下结论：

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;

什么都不起作用。我做错了什么？为什么它选择了顺序扫描？索引应该使查询快速。有没有什么可以优化的方法？

- user1754724

---

它不是执行顺序扫描，而是执行索引扫描。 - Mike Sherrill 'Cat Recall'

现在运行时间只有175毫秒。如果它需要很长时间才能运行，很可能是因为操作者有一个巨大的数据集正在通过网络传输，而不是查询本身花费了很长时间。 - Scott Marlowe

顺便说一句，effective_cache_size=3 可能有点低了。（但在这种情况下可能不会有影响） - wildplasser

你为什么认为175毫秒是“漫长的时间”？ - Bohemian

不要使用select *，因为它会增加结果集大小，需要传输到客户端。 - Clodoaldo Neto

Answer 1

CLUSTER

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

create CLUSTER ticket USING ticket_1_idx;

运行一次：

CLUSTER ticket USING ticket_1_idx;

这可以在处理大量结果集时非常有帮助。对于只返回单个或少量行的情况下，作用较小。
如果您的表不是只读的，则效果会随着时间的推移而恶化。请在合理的时间间隔内重新运行CLUSTER。Postgres会记住索引以供后续调用，因此这也有效：

CLUSTER ticket;

(但我宁愿明确地使用第一种形式。)

然而，如果你有大量的更新操作，CLUSTER（或VACUUM FULL）实际上可能会影响性能。适量的膨胀可以让UPDATE将新行版本放置在同一数据页上，避免了频繁扩展底层物理文件（代价高昂）的需要。你可以使用一个经过精心调整的FILLFACTOR来得到最佳效果：

- 顺序索引的填充因子为PK pg_repack / pg_squeeze

CLUSTER会对表进行独占锁定，在多用户环境下可能会有问题。引用手册:

当正在对表进行聚集操作时，会在其上获取一个ACCESS EXCLUSIVE锁定。 这会阻止任何其他数据库操作（包括读取和写入）在聚集操作完成之前对表进行操作。

粗体强调为本人所加。请考虑其他选择！

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 00:00'
AND    created <  '2012-12-20 00:00';

性能

为什么它选择了顺序扫描？

您的EXPLAIN输出清楚地显示了一个索引扫描，而不是一个顺序表扫描。一定有某种误解。

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

• Only query required columns instead of * to reduce transfer cost (and other performance benefits).

• Look at partitioning and put practical time slices into separate tables. Add indexes to partitions as needed.

• If partitioning is not an option, another related but less intrusive technique would be to add one or more partial indexes.
  For example, if you mostly query the current month, you could create the following partial index:

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

  CREATE a new index right before the start of a new month. You can easily automate the task with a cron job. Optionally DROP partial indexes for old months later.

  Keep the total index in addition for CLUSTER (which cannot operate on partial indexes). If old records never change, table partitioning would help this task a lot, since you only need to re-cluster newer partitions.
  Then again if records never change at all, you probably don't need CLUSTER.

性能基础知识

您可能缺少其中的基础知识。所有通常的性能建议都适用：

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

- Erwin Brandstetter