面试题：PostgreSQL条件过滤在多表复杂关联查询优化中的高级应用

SQL查询

WITH RankedScores AS (
    SELECT 
        s.student_name,
        c.course_name,
        e.score,
        -- 使用窗口函数计算成绩排名
        ROW_NUMBER() OVER (PARTITION BY s.student_id ORDER BY e.score DESC) AS rank,
        COUNT(*) OVER (PARTITION BY s.student_id) AS total_count
    FROM 
        students s
    JOIN 
        enrollments e ON s.student_id = e.student_id
    JOIN 
        courses c ON e.course_id = c.course_id
    WHERE 
        s.grade = 'Junior'
)
SELECT 
    student_name,
    course_name,
    score
FROM 
    RankedScores
WHERE 
    rank <= CEIL(total_count * 0.1);

条件过滤在复杂场景下的协同工作

多表连接：在上述查询中，首先通过JOIN操作将students、enrollments和courses三个表连接起来。PostgreSQL查询优化器会根据表的统计信息（如行数、列的基数等）决定连接的顺序。例如，如果students表中grade = 'Junior'的行数较少，优化器可能会选择先从students表过滤，再与其他表连接，这样可以减少中间结果集的大小。
子查询：在使用CTE（Common Table Expression，即上述的RankedScores）时，子查询中的条件WHERE s.grade = 'Junior'在子查询内部就进行了过滤，减少了传递到外部查询的数据量。优化器会将CTE中的操作与外部查询的操作进行合并优化，以提高整体性能。
窗口函数：窗口函数ROW_NUMBER() OVER (PARTITION BY s.student_id ORDER BY e.score DESC)和COUNT(*) OVER (PARTITION BY s.student_id)在已经经过连接和条件过滤后的结果集上进行计算。由于前面的连接和过滤操作已经减少了数据量，窗口函数的计算量也相应降低，从而提高了性能。

数据库统计信息不准确的影响

连接顺序选择错误：如果数据库统计信息不准确，查询优化器可能会选择错误的表连接顺序。例如，若认为某个大表的基数比实际小，可能会将其放在连接顺序的前面，导致产生大量中间结果，增加查询的执行时间。
索引使用不当：不准确的统计信息可能使优化器错误判断是否使用索引。如果统计信息显示某列的选择性较低（重复值多），优化器可能不会选择使用该列上的索引，即使实际上使用索引可以提高查询性能。
执行计划不佳：整体上，不准确的统计信息会导致查询优化器生成的执行计划不是最优的，可能会采用全表扫描等低效操作，而错过更高效的索引扫描、嵌套循环连接等策略，从而严重影响查询性能。

星途面试题库

面试题：PostgreSQL条件过滤在多表复杂关联查询优化中的高级应用

知识考点

面试题答案

SQL查询

条件过滤在复杂场景下的协同工作

数据库统计信息不准确的影响