获得Postgresql查询的性能

唐·卡纳利（Don Kanallie）

我有一个带有非规范化架构（1个表）的PostgreSQL数据库，其中包含约400万个条目。现在我有这个查询：

SELECT
  count(*) AS Total,
  (SELECT count(*) FROM table
   WHERE "Timestamp" > current_timestamp - INTERVAL '1 hour' AND "tableName" LIKE '%ping%') AS hour,

  (SELECT count(*) FROM table
   WHERE "Timestamp" > now() :: DATE AND "tableName" LIKE '%ping%') AS day,

  (SELECT count(*)
   FROM table
   WHERE "Timestamp" > now() :: DATE - INTERVAL '1 day' AND
         "Timestamp" <= now() :: DATE - INTERVAL '1 day' AND "tableName" LIKE '%ping%') AS yesterday,

  (SELECT count(*) FROM table
   WHERE "Timestamp" > now() :: DATE - INTERVAL '2 day' AND
         "Timestamp" <= now() :: DATE - INTERVAL '1 day' AND "tableName" LIKE '%ping%') AS "dayBeforeYesterday",

  (SELECT count(*)
   FROM table WHERE "Timestamp" > current_timestamp - INTERVAL '1 week' AND "tableName" LIKE '%ping%') AS week,

  (SELECT count(*)
   FROM table
   WHERE "Timetamp" > current_timestamp - INTERVAL '2 week' AND
        "Timestamp" < current_timestamp - INTERVAL '1 week' AND "tableName" LIKE '%ping%') AS "lastWeek",

  (SELECT count(*)
   FROM table
   WHERE "Timestamp" > current_timestamp - INTERVAL '3 week' AND
        "Timestamp" < current_timestamp - INTERVAL '2 week' AND "tableName" LIKE '%ping%') AS "weekBeforeLastWeek",

  (SELECT count(*)
   FROM table
   WHERE"Timestamp" > current_timestamp - INTERVAL '1 month' AND "tableName" LIKE '%ping%')AS month

FROM table WHERE "tableName" LIKE '%ping%';

这大约需要14秒到2分钟的时间（取决于发生了多少其他事情）。但是我的服务器是在Azure上托管了ubuntu的VM，始终将两个CPU覆盖了100％。如果我检查postgesql的统计信息，则主要是此查询，这会阻塞整个CPU。这是一个具有2核，7GB SSD的D2 VM。有没有一种方法可以在不升级我的Azure软件包的情况下加快速度？

贾尔

代替所有这些子查询，使用case表达式进行条件聚合：

SELECT
  count(*) AS Total,
  count(case when "Timestamp" > current_timestamp - INTERVAL '1 hour' AND "tableName" LIKE '%ping%' then 1 end) AS hour,
...

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。