PostgreSQL查询在使用::date、::time和interval时非常缓慢

Question

PostgreSQL查询在使用::date、::time和interval时非常缓慢

4

我有一个非常慢的SQL查询：

        select number1 from mytable
        where symbol = 25
        and timeframe = 1
        and date::date = '2008-02-05'
        and date::time='10:40:00' + INTERVAL '30 minutes'

目标是返回一个值，postgresql需要1.7秒才能返回所需的值（始终是单个值）。我需要为一个任务执行数百个这样的查询，因此速度非常慢。执行相同的查询，但直接指向时间而不使用interval和::date、::time只需要17毫秒：

    select number1 from mytable
    where symbol = 25
    and timeframe = 1
    and date = '2008-02-05 11:10:00'

我认为如果不使用::date和::time会更快，但是当我执行如下查询时：

    select number1 from mytable
    where symbol = 25
    and timeframe = 1
    and date = '2008-02-05 10:40:00' + interval '30 minutes'

我遇到了一个 SQL 错误 (22007)。我尝试了不同的变化，但是如果不使用 ::date 和 ::time，我无法使区间 interval 正常工作。Postgresql.org 上的日期/时间函数对我没有帮助。

该表在 symbol、timeframe、date 上有一个多列索引。

是否有一种快速的方法来执行添加时间的查询，或者有一个 working syntax 的区间 interval 可以使用而不需要使用 ::date 和 ::time？或者在使用这些查询时是否需要特殊的索引？

Postgresql 版本是 9.2。

编辑：该表格的格式为： date = 具有时区的时间戳， symbol，timeframe = 数值。

编辑 2：使用：

select open from ohlc_dukascopy_bid
where symbol = 25
and timeframe = 1
and date = timestamp '2008-02-05 10:40:00' + interval '30' minute

解释 shows:

"Index Scan using mcbidindex on mytable  (cost=0.00..116.03 rows=1 width=7)"
"  Index Cond: ((symbol = 25) AND (timeframe = 1) AND (date = '2008-02-05 11:10:00'::timestamp without time zone))"

现在时间大大缩短：第一次运行只需86毫秒。

- harbun

请提供完整的错误信息，而不仅仅是错误代码。 - user330315

我只在symbol、timeframe和date上有一个多列索引。再建立一个专门针对date的索引是否有意义？我原以为多列索引已经足够了。编辑：我应该运行explain来查看我的索引是否被使用。 - harbun

取决于索引的定义方式。索引中哪一列是第一列？请发布包括索引和执行计划在内的完整表定义。 - user330315

索引按照符号、时间框架和日期的顺序排列，与查询使用的顺序相同。 - harbun

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

第一个版本不会在名为date的列上使用（常规）索引。

您没有提供太多信息，但是假设名为date的列具有timestamp数据类型（而不是date），则以下操作应该可行：

and date = timestamp '2008-02-05 10:40:00' + interval '30 minutes'

如果该列的名称为date且确实是timestamp而不是date，则应使用索引。它与您的本质上相同，唯一的区别是显式的时间戳字面值（尽管Postgres也应该将'2008-02-05 10:40:00'视为时间戳字面值）。

您需要运行explain以查看是否正在使用索引。

还请更改该列的名称。使用保留字作为标识符是不好的做法，而且这是一个非常可怕的名字，无法说明该列存储了什么类型的信息。它是“开始日期”，“结束日期”，“到期日期”等等吗？