时间戳

首页 > 解决方案 > SQL 相对与绝对日期影响查询时间

sql - SQL 相对与绝对日期影响查询时间

问题描述

我运行以下查询，它需要 50 秒。

select created_at, currency, balance 
from YYY
where id in (ZZZ) and currency = 'XXX'
and created_at >= '2020-08-28'
order by created_at desc
limit 1;

解释：

Limit  (cost=100.12..1439.97 rows=1 width=72)  
->  Foreign Scan on yyy  (cost=100.12..21537.65 rows=16 width=72)
        Filter: (("substring"((object_key)::text, '\w+:(\d+):.*'::text))::integer = 723120)

然后我运行以下查询，它“无限”的时间。等到结束的时间太长了。

select created_at, currency, balance 
from YYY
where id in (ZZZ) and currency = 'XXX'
and created_at >= NOW() - INTERVAL '1 DAY'
order by created_at desc
limit 1;

解释：

Limit  (cost=53293831.90..53293831.91 rows=1 width=72)
  ->  Result  (cost=53293831.90..53293987.46 rows=17284 width=72)
        ->  Sort  (cost=53293831.90..53293840.54 rows=17284 width=556)
              Sort Key: yyy.created_at DESC
              ->  Foreign Scan on yyy  (cost=100.00..53293814.62 rows=17284 width=556)
                    Filter: ((created_at >= (now() - '1 day'::interval)) AND (("substring"((object_key)::text, '\w+:(\d+):.*'::text))::integer = 723120))

是什么让这些查询之间产生了巨大的差异。我知道索引是用来提高性能的。我们可以从这里推断出什么？任何贡献将不胜感激。

标签： sqlpostgresqlquery-performance

解决方案

使用文字，优化器可以轻松地使用正确的索引来计划有效的数据访问。

使用类似 的表达式NOW - INTERVAL '4 DAY'，您至少会遇到两个挑战：

  • 它是一个稳定的，而不是一成不变的表达式。更别说文字了。
  • 表达式是 a TIMESTAMP WITH TIME ZONE，而不是 a DATE，并且您需要隐式类型转换。

你只是让优化器的生活变得困难......

yyy我刚刚在我的 PostgreSQL 数据库中创建了一个包含 12 年不同日期的单列表。没有索引。您已经在这里看到了解释计划的成本差异。

$ psql -c "explain select * from yyy where created_at >= '2020-08-28'"
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on yyy  (cost=0.00..74.79 rows=126 width=4)
   Filter: (created_at >= '2020-08-28'::date)

和：

$ psql -c "explain select * from yyy where created_at >= now() - interval '4 day'"
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on yyy  (cost=0.00..96.70 rows=126 width=4)
   Filter: (created_at >= (now() - '4 days'::interval))
(2 rows)

索引的存在将是一个更糟糕的差异......

推荐阅读