面试题：PostgreSQL查询优化器在处理复杂查询时如何综合利用多方面统计信息

优化器整合和权衡统计信息生成最优查询计划的方式

关联列统计信息的利用
- 基数估计：优化器使用关联列的统计信息来估计连接操作后产生的行数。例如，如果有两个表 A 和 B 通过列 a_id 和 b_id 连接，优化器会查看 a_id 和 b_id 上的统计信息，如不同值的数量（基数），以计算连接结果集的大致大小。如果 A 表中 a_id 列有 1000 个不同值，B 表中 b_id 列有 500 个不同值，假设均匀分布，优化器可能估计连接结果集的基数在一定范围内（如果是内连接，最大可能是 500 * 1000，实际会根据数据分布调整）。
- 数据分布：了解关联列的数据分布有助于优化器选择合适的连接算法。如果关联列的数据分布倾斜严重，例如 A 表中大部分 a_id 值集中在少数几个值上，哈希连接可能比嵌套循环连接更有效，因为哈希连接在处理倾斜数据时性能更好。
分区统计信息的利用
- 分区裁剪：当表是分区表时，优化器利用分区统计信息来确定哪些分区需要参与查询。例如，对于一个按日期分区的销售表，查询特定月份的销售数据时，优化器可以根据分区统计信息直接排除不相关的分区，从而大大减少扫描的数据量。如果销售表按月份分区，查询 2023 年 10 月的数据，优化器能通过分区统计知道只有 202310 这个分区需要参与查询，避免扫描其他月份的分区。
- 跨分区连接：在涉及多个分区表连接时，优化器会根据分区统计信息决定连接顺序和方式。如果两个分区表 C 和 D 有相同的分区键并且按相同方式分区，优化器可能选择在每个分区内进行连接，而不是先跨分区全表扫描再连接，这样可以减少中间结果集的大小，提高查询效率。
权衡不同统计信息
- 成本模型：PostgreSQL 优化器使用成本模型来权衡不同操作的成本。对于连接操作，成本模型会考虑关联列统计信息计算的连接基数、连接算法的成本（如哈希连接和嵌套循环连接的不同成本）；对于分区操作，成本模型会考虑分区裁剪减少的数据量和跨分区操作的额外开销。优化器会尝试不同的查询计划（不同的连接顺序、不同的连接算法等），计算每个计划的总成本，选择成本最低的计划作为最优查询计划。
- 统计信息的时效性：优化器会考虑统计信息的时效性。如果统计信息过旧，可能导致估计的基数不准确，从而生成次优的查询计划。因此，PostgreSQL 提供了更新统计信息的机制，如 ANALYZE 命令，用户需要定期运行该命令以确保统计信息的准确性，使优化器能做出更合理的决策。

复杂查询场景及优化过程示例

复杂查询场景 假设我们有三个表：orders（订单表）、order_items（订单项表）和 products（产品表）。
- orders 表结构：(order_id, customer_id, order_date)
- order_items 表结构：(item_id, order_id, product_id, quantity, price)
- products 表结构：(product_id, product_name, category) 查询需求是获取每个客户在 2023 年购买的每个产品类别中价格最高的订单项信息，包括客户 ID、产品类别、最高价格订单项的价格和数量。

WITH filtered_orders AS (
    SELECT order_id
    FROM orders
    WHERE order_date >= '2023 - 01 - 01' AND order_date < '2024 - 01 - 01'
),
joined_items AS (
    SELECT oi.order_id, oi.product_id, oi.quantity, oi.price, p.category
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE oi.order_id IN (SELECT order_id FROM filtered_orders)
),
max_price_per_category AS (
    SELECT category, MAX(price) AS max_price
    FROM joined_items
    GROUP BY category
)
SELECT jo.customer_id, jo.category, jo.price, jo.quantity
FROM joined_items jo
JOIN max_price_per_category mp ON jo.category = mp.category AND jo.price = mp.max_price;

优化过程
- 分析统计信息：
  - 首先，优化器会查看 orders 表中 order_date 列的统计信息，估计 filtered_orders 子查询返回的行数。如果 order_date 列有良好的统计信息，优化器能准确判断出 2023 年订单的大致数量。
  - 对于 order_items 表和 products 表连接的 product_id 列，优化器利用其关联列统计信息估计 joined_items 子查询连接结果的基数。同时，order_items 表中 order_id 列的统计信息会影响子查询 WHERE oi.order_id IN (...) 的执行效率。
  - 在 max_price_per_category 子查询中，优化器根据 joined_items 子查询结果集中 category 列的统计信息来估计分组操作的成本和结果集大小。
- 优化查询计划：
  - 优化器可能会将 orders 表的过滤条件提前到 joined_items 子查询的连接中，避免先执行子查询再进行连接。例如，将查询改写为：

SELECT oi.customer_id, p.category, oi.price, oi.quantity
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= '2023 - 01 - 01' AND o.order_date < '2024 - 01 - 01'
  AND (oi.price, oi.category) IN (
      SELECT MAX(price), category
      FROM order_items oi
      JOIN products p ON oi.product_id = p.product_id
      JOIN orders o ON oi.order_id = o.order_id
      WHERE o.order_date >= '2023 - 01 - 01' AND o.order_date < '2024 - 01 - 01'
      GROUP BY p.category
  );

 - 这样改写后，优化器可以利用表之间的关联列统计信息和分区统计信息（如果表是分区表），更有效地进行连接操作和子查询优化。例如，如果 `orders` 表按日期分区，优化器可以直接裁剪出 2023 年的分区，减少扫描的数据量。同时，新的连接顺序和子查询结构可能使优化器选择更合适的连接算法，如哈希连接，从而提高查询性能。

星途面试题库

面试题：PostgreSQL查询优化器在处理复杂查询时如何综合利用多方面统计信息

知识考点

面试题答案

优化器整合和权衡统计信息生成最优查询计划的方式

复杂查询场景及优化过程示例