postgresql - 在具有 > 12M 行的表中的 Postgresql 中查询太慢

Question

在我的网络应用程序中，我有一个每次增长超过 1200 万行的简单表格。

+-----+-----+------+-------+--------+
| id  | dtt | cus  | event | server |
-------------------------------------

我正在使用此查询获取客户今天的事件数

SELECT COUNT(*)  FROM events
    WHERE dtt AT TIME ZONE 'America/Santiago' >=date(now() AT TIME ZONE 'America/Santiago') + interval '1s' 
    AND cus=2

而且我的网络应用程序的性能非常糟糕:22702 毫秒。

"Aggregate  (cost=685814.54..685814.55 rows=1 width=0) (actual time=21773.451..21773.452 rows=1 loops=1)"
"  ->  Seq Scan on events  (cost=0.00..675644.52 rows=4068008 width=0) (actual time=10277.508..21732.548 rows=409808 loops=1)"
"        Filter: ((cus = 2) AND (timezone('America/Santiago'::text, dtt) >= (date(timezone('America/Santiago'::text, now())) + '00:00:01'::interval)))"
"        Rows Removed by Filter: 12077798"
"Planning time: 0.127 ms"
"Execution time: 21773.509 ms"

我创建了下一个索引:

CREATE INDEX events_dtt_idx
  ON events
  USING btree
  (dtt);

CREATE INDEX events_id_desc
  ON events
  USING btree
  (id DESC NULLS LAST);

CREATE INDEX events_cus_idx
  ON events
  USING btree
  (cus);

CREATE INDEX events_id_idx
  ON events
  USING btree
  (id);

使用 Postgresql 9.4，Linux x64

我该如何改进？提前致谢。

Answer 1

类似于:

CREATE INDEX dtt_tz_idx ON  events (DATE(dtt AT TIME ZONE 'America/Santiago'));

然后查询

SELECT COUNT(*)  FROM events
    WHERE DATE(TIMEZONE('America/Santiago'::text, dtt)) >=date(now() AT TIME ZONE 'America/Santiago') + interval '1s' 
    AND cus=2

如果它不起作用，请在 psql 中尝试“\d dtt_tz_idx”并尝试将查询中的数据类型与索引相匹配。