performance - PostgreSQL - 如何减少 select 语句的执行时间

Question

• 我的 Postgres 版本:“PostgreSQL 9.4.1，由 Visual C++ build 编译1800，32 位”
• 我要处理的表；包含列
  1. 事件时间 - 没有时区的时间戳
  2. 序列号 - 字符变化(32)
  3. sourceid - 整数

和其他 4 列

这是我的选择语句

SELECT eventtime, serialnumber
    FROM   t_el_eventlog
    WHERE
    eventtime at time zone 'CET'  >  CURRENT_DATE  and
    sourceid = '14';

上述查询的执行时间为59647ms

在我的 r 脚本中，我有 5 个这样的查询(执行时间 = 59647ms*5)。如果不使用时区“CET”，执行时间会非常短 - 但在我的情况下，我必须使用时区“CET”，如果我是对的，执行时间长是因为这些时区。

我的查询计划

文本查询

解释分析查询(不带时区)

无论如何我可以减少我的选择语句的查询执行时间

Answer 1

由于我不知道值的分布，所以没有解决问题的明确方法。

但有一个问题很明显:eventtime 列有一个索引，但由于查询是在该列上使用一个函数进行操作，因此无法使用该索引。

eventtime in time zone 'UTC' > CURRENT_DATE

要么必须删除索引并使用该函数重新创建索引，要么必须重写查询。

重新创建索引(示例):

CREATE INDEX ON t_el_eventlog (timezone('UTC'::text, eventtime));

(这与 eventtime in time zone 'UTC' 相同)

这个用function匹配filter，可以用index。

我怀疑 sourceid 没有很好的分布，没有太多不同的值。在这种情况下，删除 sourceid 上的索引并删除 eventtime 上的索引并在 eventtime 和 sourceid 上创建新索引可能是一个想法:

CREATE INDEX ON t_el_eventlog (timezone('UTC'::text, eventtime), sourceid);

这就是理论告诉我们的。我围绕它做了一些测试，有一个大约有 1000 万行的表，事件时间分布在 36 小时内，只有 20 个不同的 sourceids (1..20)。分布非常随机。最好的结果是在事件时间的索引、sourceid(无函数索引)和调整查询中。

CREATE INDEX ON t_el_eventlog (eventtime, sourceid);
-- make sure there is no index on source id. we need to force postgres to this index.

-- make sure, postgres learns about our index
ANALYZE; VACUUM;

-- use timezone function on current date (guessing timezone is CET)
SELECT * FROM t_el_eventlog
 WHERE eventtime > timezone('CET',CURRENT_DATE) AND sourceid = 14;

对于具有 10'000'000 行的表，此查询仅在 400 毫秒内返回了大约 500'000 行。 (而不是所有其他组合中的大约 1400 到 1700)。

找到索引和查询之间的最佳匹配是一项任务。我建议进行一些研究，建议是 http://use-the-index-luke.com

---

这是最后一种方法的查询计划:

Index Only Scan using evlog_eventtime_sourceid_idx on evlog  (cost=0.45..218195.13 rows=424534 width=0)
     Index Cond: ((eventtime > timezone('CET'::text, (('now'::cstring)::date)::timestamp with time zone)) AND (sourceid = 14))

如您所见，这是一个绝配...