优化 BETWEEN 日期语句

Question

我可以帮您优化一个使用BETWEEN和timestamp字段的Postgres查询。

我有两张表：

ONE(int id_one(PK), datetime cut_time, int f1 ...) 

包含约3394行的内容

TWO(int id_two(PK), int id_one(FK), int f2 ...) 

包含约4000000行数据。

id_one和id_two的主键，id_one和cut_time的外键都有B树索引。

我想执行以下查询：

select o.id_one, Date(o.cut_time), o.f1, t.f2 
from one o
inner join two t ON (o.id_one = t.id_one)
where o.cut_time between '2013-01-01' and '2013-01-31';

这个查询在大约7秒钟内检索了约1,700,000行。

下面报告了explain analyze报告：

Merge Join  (cost=20000000003.53..20000197562.38 rows=1680916 width=24) (actual time=0.017..741.718 rows=1692345 loops=1)"
  Merge Cond: (c.coilid = hf.coilid)
  ->  Index Scan using pk_coils on coils c  (cost=10000000000.00..10000000382.13 rows=1420 width=16) (actual time=0.008..4.539 rows=1404 loops=1)
        Filter: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time <= '2013-01-31 00:00:00'::timestamp without time zone))
        Rows Removed by Filter: 1990
  ->  Index Scan using idx_fk_lf_data on hf_data hf  (cost=10000000000.00..10000166145.90 rows=4017625 width=16) (actual time=0.003..392.535 rows=1963386 loops=1)
Total runtime: 768.473 ms

时间戳列上的索引未被使用。如何优化此查询？

- Nko

---

你有禁用规划器选项吗？ cost=10000000000.00的成本看起来非常可疑。 - Richard Huxton

我如何为计划程序选项设置默认值？ - Nko

我在想你可能会将其中一个或多个设置为关闭：http://www.postgresql.org/docs/9.2/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE - Richard Huxton

Answer 1

正确的DDL脚本

一个正确的设置可能如下所示:

db<>fiddle 这里
_{旧版sqlfiddle}

关于这个 fiddle 的更多信息请见下文。
假设数据类型为timestamp，列名为 datetime。

错误的查询语句

BETWEEN 与 timestamp 列通常是不正确的。请参阅：

• 在 PostgreSQL 中查找重叠的日期范围

在您的查询中：

SELECT o.one_id, date(o.cut_time), o.f1, t.f2 
FROM   one o
JOIN   two t USING (one_id)
WHERE  o.cut_time BETWEEN '2013-01-01' AND '2013-01-31';

字符串常量'2013-01-01'和'2013-01-31'被强制转换为时间戳'2013-01-01 00:00'和'2013-01-31 00:00'。这将排除大部分1月31日。时间戳'2013-01-31 12:00'将不符合要求，这显然是错误的。
如果您使用'2013-02-01'作为上限，则会包括'2013-02-01 00:00'。但仍然是错误的。

要获取“2013年1月”的所有时间戳，需要更改为：

SELECT o.one_id, date(o.cut_time), o.f1, t.f2 
FROM   one o
JOIN   two t USING (one_id)
WHERE  o.cut_time <b>>=</b> '2013-01-01'
AND    o.cut_time <b><</b>  '2013-02-01';

排除上限。

优化查询

检索1.7百万行数据可能是没有意义的。在检索结果之前进行聚合。

由于表two非常大，因此从中检索多少行非常重要。当检索超过 ~ 5％时，通常不会使用纯粹的two.one_id索引，因为直接顺序扫描表速度更快。

您的表统计信息已过时，或者您已经混淆了成本常数和其他参数（显然如下）以强制Postgres仍使用该索引。

我看到在two上使用索引的唯一机会是使用覆盖索引：

CREATE INDEX two_one_id_f2 ON two(one_id, f2);

这样，如果满足某些前提条件，Postgres就可以直接从索引中读取。可能会快一点，但没多少。没有测试。 < h3 > EXPLAIN 输出中的奇怪数字 至于您在 EXPLAIN ANALYZE 中看到的奇怪数字。这个示例应该能解释清楚。

似乎您已经使用了这些调试设置：

SET enable_seqscan = off;
SET enable_indexscan = off;
SET enable_bitmapscan = off;

所有这些都应该是打开（默认设置），除了调试。否则会影响性能! 可以使用以下命令进行检查：

SELECT * FROM pg_settings WHERE name ~~ 'enable%';

- Erwin Brandstetter

---

BETWEEN 与 timestamp 的使用上你讲得很好，非常感谢你的分享。我学到了新知识:) - marcj

感谢Erwin提供的覆盖索引提示。很抱歉，我忘记告诉你我正在使用Postgresql 9.2。 - Nko

Answer 2

查询执行时间少于1秒。其他6秒以上的时间用于服务器和客户端之间的通信。

- Clodoaldo Neto

---

谢谢Neto，好的，查询执行的有效时间少于一秒钟。当我开发我的解决方案时，我会在客户端和服务器之间的流量中看到这种延迟吗？ - Nko

@Nko 当然可以。可能更糟糕。你需要170万行吗？ - Clodoaldo Neto

@Nko...Neto是正确的。你现在优化日期子句不会获得很多收益。检索的记录数是主要问题。 - marcj

好的，谢谢。这只是我针对这种数据的第一种尝试，我肯定需要聚合这些检索到的数据，以此来减少总执行时间。谢谢。 - Nko