PostgreSQL-如何减少select语句导出时间

Question

• 我的Postgres版本：“PostgreSQL 9.4.1，由Visual C++build 1800编译，32位”
• 我要处理的桌子；包含列
  1. EventTime-Timestamp不带时区
  2. SerialNumber-可变字符(32)
  3. sourceid-integer

和其他4列

下面是我的select语句

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

Answer 1

由于数值的分布对我来说是未知的，所以没有明确的方法来解决这个问题。

但是有一个问题很明显：eventtime列有一个索引，但是由于查询在该列上使用函数操作，所以不能使用索引。

eventtime in time zone 'UTC' > CURRENT_DATE

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

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

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

这就是理论告诉我们的。我对此做了一些测试，使用了一个大约有1000万行的表、36小时内的eventtime分布以及只有20个不同的SourceID(1..20)。分布是非常随机的。最好的结果是在eventtime、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行，该查询仅在400ms内返回大约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))