优化多个联接

优化查询时,始终要考虑两件事:

• 可以使用哪些索引(可能需要创建索引)
• 如何编写查询(您可能需要更改查询,以便查询优化程序能够找到适当的索引,并且不会重复读取数据)

一些观察:

• 在加入日期之前,您正在执行日期操作。作为一般规则,这将阻止查询优化程序使用索引,即使它存在。您应该尝试以这样的方式编写表达式:索引列在表达式的一侧是不变的。

• 子查询筛选的日期范围与 generate_series . 这是一个重复,它限制了优化者选择最有效优化的能力。我怀疑这可能是为了提高性能而写的,因为优化程序无法在日期列上使用索引。( body_time )?

• 注意 :实际上,我们非常希望在 Body.body_time

• ORDER BY 在子查询中,最多是冗余的。最坏情况下,它可能会强制查询优化程序在联接之前对结果集进行排序;这不一定对查询计划有好处。相反,只在最后显示时应用排序。

• 使用 LEFT JOIN 在您的子查询中是不适当的。假设您使用的是 NULL 行为(你应该如此),任何 外面的 加入到 envelope 会回来 envelope_command=NULL ,这些将被排除在条件之外。 envelope_command=? .

• 子查询 o 和 i 除了 envelope_command 价值。这将强制优化程序扫描同一个基础表两次。你可以使用 数据透视表 技术将数据连接一次,并将值拆分为两列。

尝试以下使用Pivot技术的方法:

SELECT  p.period,
        /*The pivot technique in action...*/
        SUM(
        CASE WHEN envelope_command = 1 THEN body_size
        ELSE 0
        END) AS Outbound,
        SUM(
        CASE WHEN envelope_command = 2 THEN body_size
        ELSE 0
        END) AS Inbound
FROM    (
        SELECT  date '2009-10-01' + s.day AS period
        FROM    generate_series(0, date '2009-10-31' - date '2009-10-01') AS s(day)
        ) AS p 
        /*The left JOIN is justified to ensure ALL generated dates are returned
          Also: it joins to a subquery, else the JOIN to envelope _could_ exclude some generated dates*/
        LEFT OUTER JOIN (
        SELECT  b.body_size,
                b.body_time,
                e.envelope_command
        FROM    body AS b 
                INNER JOIN envelope e 
                  ON e.message_id = b.message_id 
        WHERE   envelope_command IN (1, 2)
        ) d
          /*The expressions below allow the optimser to use an index on body_time if 
            the statistics indicate it would be beneficial*/
          ON d.body_time >= p.period
         AND d.body_time < p.period + INTERVAL '1 DAY'
GROUP BY p.Period
ORDER BY p.Period

编辑 :增加了tom h建议的过滤器。

建在克雷格·杨的房子上 suggestions ,这里是对我正在处理的数据集的修改后的查询,它在~1.8秒内运行。这是对原始的~2.0年代的一个微小的改进,和对克雷格的一个巨大的改进,花费了~22秒。

SELECT
    p.period,
    /* The pivot technique... */
    SUM(CASE envelope_command WHEN 1 THEN body_size ELSE 0 END) AS Outbound,
    SUM(CASE envelope_command WHEN 2 THEN body_size ELSE 0 END) AS Inbound
FROM
(
    /* Get days range */
    SELECT date '2009-10-01' + day AS period
    FROM generate_series(0, date '2009-10-31' - date '2009-10-01') AS day
) p
    /* Join message information */
    LEFT OUTER JOIN
    (
        SELECT b.body_size, b.body_time::date, e.envelope_command
        FROM body AS b 
            INNER JOIN envelope e ON e.message_id = b.message_id 
        WHERE
            e.envelope_command IN (2, 1)
            AND b.body_time::date BETWEEN (date '2009-10-01') AND (date '2009-10-31')
    ) d ON d.body_time = p.period
GROUP BY p.period
ORDER BY p.period

几天前,我卸载了我的PostgreSQL服务器,所以你可能需要尝试一下,但希望这对你来说是一个好的开始。

关键是:

1. 您不需要子查询-只需要直接连接和聚合
2. 您应该能够使用内部联接,这通常比外部联接更具性能。

如果没有别的,我认为下面的查询会更清楚一些。

我在查询中使用了一个日历表,但是您可以在使用它时用generate_系列替换它。

此外,根据索引的不同,最好将正文日期与>=和<进行比较,而不是抽出日期部分进行比较。我对PostgreSQL的了解还不够,不知道它在幕后是如何工作的,所以我会尝试两种方法,看看哪个服务器可以更好地优化。在伪代码中,您将执行以下操作:body_date>=date(time=午夜)和body_date<date+1(time=午夜)。

SELECT
    CAL.calendar_date AS period,
    SUM(O.body_size) AS outbound,
    SUM(I.body_size) AS inbound
FROM
    Calendar CAL
INNER JOIN Body OB ON
    OB.body_time::date = CAL.calendar_date
INNER JOIN Envelope OE ON
    OE.message_id = OB.message_id AND
    OE.envelope_command = 1
INNER JOIN Body IB ON
    IB.body_time::date = CAL.calendar_date
INNER JOIN Envelope IE ON
    IE.message_id = IB.message_id AND
    IE.envelope_command = 2
GROUP BY
    CAL.calendar_date