Postgres：优化按日期时间查询

Question

我有一个表，其中有一个日期时间字段“updated_at”。我很多查询都会使用范围查询来查询此字段，例如更新日期大于某个特定日期的行。

我已经为updated_at添加了索引，但即使我对返回行数设置限制，我的大部分查询仍然非常慢。

除了添加索引外，我还能做什么来优化查询日期时间字段的查询？

- Henley

---

你能发布执行计划、总行数和“非常慢”确切的值吗？ - Jakub Kania

请阅读http://stackoverflow.com/tags/postgresql-performance/info（以及链接的SlowQueryQuestions wiki页面），然后使用适当的“explain analyze”结果更新您的问题并报告回来。由于您正在使用查询生成器，您可能需要使用“auto_explain”或记录查询并手动重新执行它们。 - Craig Ringer

请发布模式和缓慢查询的类型。由于问题的措辞不够清晰，无法合理地回答... - Denis de Bernardy

Answer 1

通常情况下，数据库优化器不会选择针对开放范围（如 updated_at > somedate）使用索引。

但是，在许多情况下，日期时间列不会超过"现在"，因此您可以通过使用between将条件转换为一个范围来保留> somedate的语义，例如：

where updated_at between somedate and current_timestamp

BETWEEN谓词更有可能导致优化器选择使用索引。

---

如果这种方法提高了查询的性能，请发布结果。

- Bohemian

---

PostgreSQL是否真的如此？我认为优化器将查看相关列中的值范围（通过pg_statistics），并针对谓词生成预估的结果集基数。如果最大值小于或等于current_timestamp，则我认为差异不会很大。但是测试一下会很有趣--执行计划将揭示一切。 - David Aldridge

Postgres如果有用的话，将会使用索引来执行大于（>）操作，无需使用between操作符。请参考这里的示例http://sqlfiddle.com/#!12/e3142/3 。就像通常使用索引一样，一切都取决于使用索引的成本是否比其他操作更低。 - user330315

在Postgres中，“>”已经被优化得很好了，并且根据表格统计数据，在适当的情况下使用BTree索引。 - Denis de Bernardy

在Redshift上使用between大大提高了我的性能。 - Paul Odeon

通过使用>，我们得到了Seq Scan on wiki_helpfulvote (cost=0.00..1811033.95 rows=18614372 width=635)。通过使用between X and current_timestamp，我们得到了Index Scan using wiki_helpfulvote_created_06b8907e on wiki_helpfulvote (cost=0.57..1008752.89 rows=279216 width=6。这似乎是一个明显的优势。也许PostgreSQL使用current_timestamp来意识到它不必担心未来的写入，并且不需要表锁定。 - undefined

Answer 2

针对任何查询，使用索引取决于使用该索引的成本与顺序扫描的成本之间的比较

开发人员通常认为，由于存在索引，查询应该运行得更快，如果查询运行缓慢，则索引是解决方案。但是，随着结果中元组数量的增加，使用索引的成本可能会增加。

您正在使用postgres。Postgres不支持围绕给定属性进行聚类。这意味着当postgres面临区间查询（如att>a和att<b）时，需要计算结果中元组数量的估计值（确保频繁清理数据库）以及使用索引与执行顺序扫描相比的成本。然后它将决定使用什么方法。

您可以通过运行以下命令来检查此决策：

EXPLAIN ANALYZE <query>; 

在psql中，它会告诉你是否使用索引。如果您真的非常想使用索引而不是顺序扫描（有时确实需要），并且您真的非常清楚自己在做什么，您可以更改计划程序常量中顺序扫描的成本或禁用顺序扫描，以支持任何其他方法。有关详细信息，请参见此页面：http://www.postgresql.org/docs/9.1/static/runtime-config-query.html。请确保浏览正确版本的文档。--dmg

- dmg

Answer 3

我在一张近100万行的表中遇到了类似的情况。

因此，我在visited_at（日期时间字段）上创建了一个B树索引，并尝试查询所有行：

最初的回答：

explain analyze select mes,count(usuario) as usuarios
from (
   SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at), extract(month from visited_at))   AS mes
     FROM pageview 
     ) as usuarios
group by 1
order by 1

我理解的是：

GroupAggregate (cost=445468.78..451913.54 rows=200 width=64) (actual time=31027.876..31609.754 rows=8 loops=1)
-> Sort (cost=445468.78..447616.37 rows=859035 width=64) (actual time=31013.501..31439.350 rows=358514 loops=1)
Sort Key: usuarios.mes
Sort Method: external merge Disk: 24000kB
-> Subquery Scan on usuarios (cost=247740.16..263906.75 rows=859035 width=64) (actual time=23121.403..28200.175 rows=358514 loops=1)
-> Unique (cost=247740.16..255316.40 rows=859035 width=48) (actual time=23121.400..28129.538 rows=358514 loops=1)
-> Sort (cost=247740.16..250265.57 rows=1010166 width=48) (actual time=23121.399..27559.241 rows=1010702 loops=1)
Sort Key: (COALESCE(pageview.usuario, (pageview.ip)::text)), (ROW(date_part('year'::text, pageview.visited_at), date_part('month'::text, pageview.visited_at)))
Sort Method: external merge Disk: 66944kB
-> Seq Scan on pageview (cost=0.00..84842.49 rows=1010166 width=48) (actual time=0.012..1909.324 rows=1010702 loops=1)
Total runtime: 31632.012 ms

这意味着在创建索引之前的查询没有任何改进。但是，我将行数减少到当前日期的前31天。

最初的回答：No improvement on pre-index query. Reduced rows to current_date-31.

explain analyze select mes,count(usuario) as usuarios
from (
   SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at), extract(month from visited_at))   AS mes
     FROM pageview 
     where visited_at > current_date - 31
     ) as usuarios
group by 1
order by 1

and got

 -> Sort (cost=164735.62..165310.93 rows=230125 width=64) (actual time=9532.343..9602.743 rows=90871 loops=1)
Sort Key: usuarios.mes
Sort Method: external merge Disk: 5872kB
-> Subquery Scan on usuarios (cost=122598.79..126929.62 rows=230125 width=64) (actual time=7251.344..9178.901 rows=90871 loops=1)
-> Unique (cost=122598.79..124628.37 rows=230125 width=48) (actual time=7251.343..9157.837 rows=90871 loops=1)
-> Sort (cost=122598.79..123275.32 rows=270610 width=48) (actual time=7251.341..8932.541 rows=294915 loops=1)
Sort Key: (COALESCE(pageview.usuario, (pageview.ip)::text)), (ROW(date_part('year'::text, pageview.visited_at), date_part('month'::text, pageview.visited_at)))
Sort Method: external merge Disk: 18864kB
-> Bitmap Heap Scan on pageview (cost=5073.60..81528.85 rows=270610 width=48) (actual time=111.950..1877.603 rows=294915 loops=1)
Recheck Cond: (visited_at > (('now'::cstring)::date - 31))
Rows Removed by Index Recheck: 338268
-> Bitmap Index Scan on visited_at_index (cost=0.00..5005.94 rows=270610 width=0) (actual time=109.874..109.874 rows=294915 loops=1)
Index Cond: (visited_at > (('now'::cstring)::date - 31))
Total runtime: 9687.460 ms

我对将datetime转换为date进行了小幅改进（visited_at :: date）。

最初的回答

explain analyze select mes,count(usuario) as usuarios
from (
   SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at::date), extract(month from visited_at::date))   AS mes
     FROM pageview 
     where visited_at::date > current_date - 31
     ) as usuarios
group by 1
order by 1

and got

GroupAggregate (cost=201976.97..204126.56 rows=200 width=64) (actual time=9040.196..9102.098 rows=2 loops=1)
-> Sort (cost=201976.97..202692.83 rows=286345 width=64) (actual time=9035.624..9058.457 rows=88356 loops=1)
Sort Key: usuarios.mes
Sort Method: external merge Disk: 5704kB
-> Subquery Scan on usuarios (cost=149102.66..154491.53 rows=286345 width=64) (actual time=7511.231..8840.270 rows=88356 loops=1)
-> Unique (cost=149102.66..151628.08 rows=286345 width=48) (actual time=7511.229..8823.647 rows=88356 loops=1)
-> Sort (cost=149102.66..149944.47 rows=336722 width=48) (actual time=7511.227..8666.667 rows=287614 loops=1)
Sort Key: (COALESCE(pageview.usuario, (pageview.ip)::text)), (ROW(date_part('year'::text, ((pageview.visited_at)::date)::timestamp without time zone), date_part('month'::text, ((pageview.visited_at)::date)::timestamp without time zone)))
Sort Method: external merge Disk: 18408kB
-> Seq Scan on pageview (cost=0.00..97469.57 rows=336722 width=48) (actual time=0.018..1946.139 rows=287614 loops=1)
Filter: ((visited_at)::date > (('now'::cstring)::date - 31))
Rows Removed by Filter: 722937
Total runtime: 9108.644 ms

这是对我有效的调整：

1）索引B树（主要） 2）转换为日期（微小差异）

10秒仍然是对用户响应的很大时间。

因此，我的解决方案是创建表月_用户并仅使用一次。

最初的回答

insert from month_users select mes,count(usuario) as usuarios
from (
   SELECT distinct coalesce(usuario, ip) as usuario, (extract(year from visited_at), extract(month from visited_at))   AS mes
     FROM pageview 
     ) as usuarios
group by 1
order by 1

最初的回答

并使用

select * from month_users

results:

Seq Scan on usuarios_mes (cost=0.00..21.30 rows=1130 width=42) (actual time=0.302..0.304 rows=8 loops=1)
Total runtime: 0.336 ms

现在有一个可接受的结果！

但是最终的解决方案仍然需要考虑如何定期更新表格结果。

Original Answer翻译成"最初的回答"

- Alexandre Andrade

Answer 4

假设索引正在使用但性能仍然很差，我唯一想到的解决方法是通过该索引对表进行聚集：http://www.postgresql.org/docs/9.1/static/sql-cluster.html 这将使具有相同update_at值的行在物理上被共同定位，提高了通过索引访问该表的查询的性能，特别是对于大范围扫描。

注意文档中的警告，并注意随着行的更新，聚集不会保留。

此外：

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

基于这些限制，它可能不是您情况下的可行解决方案，但对于其他人可能有用。

- David Aldridge