代码之家 › 专栏 › 技术社区 › BaoTrung Tran

函数用于在大表上执行日期和时间间隔的慢速查询

database-indexes database-performance postgresql sql

BaoTrung Tran · 技术社区 · 4 年前

我使用的是PostgreSQL,我使用以下查询:

SELECT r.name,count(r.name)
from rooms r
where to_date(dateinput,'YYYYMMDD') between r.start_date and r.end_date
or to_char(r.end_time,'HH24:MI:SS')<> '00:00:00')
and (r.name in ('nameA','nameB'))
group by r.name

当我的桌子有90万行时,速度非常慢。我在列上创建索引 start_date , end_date 和 name .执行时间为1543毫秒。 end_time 属于数据类型 time without time zone .

但是当我把查询改为

SELECT r.name,count(r.name)
from rooms r
where cast(dateinput as date) >= r.start_date
  and cast(dateinput as date) < r.end_date
  and r.name in ('nameA','nameB')
or to_char(r.end_time,'HH24:MI:SS')<> '00:00:00')
and (r.name in ('nameA','nameB'))
group by r.name

执行时间缩短到786毫秒 to_date 和 between 使索引不可操作。我找不到任何文档或示例来解释为什么在使用时不能使用索引 迄今为止 和 之间 .我不知道为什么修改查询会将时间缩短到786毫秒。有人能帮我吗?

0 回复 | 直到 4 年前

Laurenz Albe 4 年前

索引不能用于这两个查询,执行时间的差异可能是因为第一个查询必须从磁盘读取更多数据,而在第二个查询期间这些数据已经缓存在RAM(共享缓冲区)中。

奇怪的 OR 条件使此查询很难高效,并且 to_char(r.end_time,'HH24:MI:SS') 是不可能索引的(我不明白它的意思)。

您将不得不重写查询,而不是 或 (使用 UNION )并在 end_time 不同的是,你可以使用索引来加速。

我会这样重写查询:

SELECT r.name, count(r.name)
FROM (SELECT r.name
      FROM rooms r
      WHERE to_date(dateinput,'YYYYMMDD') <@ daterange(r.start_date, r.end_date, '[]')
        AND r.name IN ('nameA','nameB')
      UNION
      SELECT r.name
      FROM rooms r
      WHERE r.end_time <> TIME '00:00:00'
        AND r.name IN ('nameA','nameB')
     ) AS r
GROUP BY r.name;

这些指数可能有助于:

CREATE INDEX ON rooms USING gist (daterange(r.start_date, r.end_date, '[]')) WHERE r.name IN ('nameA','nameB');

CREATE INDEX ON rooms (name) WHERE r.end_time <> TIME '00:00:00' AND r.name IN ('nameA','nameB');

推荐文章