sql - 提高 Postgresql 查询的性能

Question

我有一个带有非规范化架构(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 的虚拟机，两个 CPU 的覆盖率始终为 100%。如果我检查postgesql的统计信息，主要是这个查询，它阻塞了整个CPU。这是一个具有 2 个核心、7GB SSD 的 D2 VM。有没有办法在不升级 Azure 包的情况下加快速度？

Answer 1

使用 case 表达式进行条件聚合，而不是所有这些子查询:

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