如何优化SQL中近乎重复的子查询以提升查询性能？

Hey there! Let's fix that slow query you're dealing with—those repetitive subqueries are definitely dragging down performance, and since they all share almost identical logic, we can refactor this to run way more efficiently.

The Problem with Your Current Query #

Each of those scalar subqueries runs once per row in your main result set, which means you're repeating the same table joins (tableQ → tableQL → tableQLA → tableQa) dozens or hundreds of times. That's a huge waste of database resources.

Optimized Solution: Precompute Values with a CTE + Conditional Aggregation #

We can precompute all the needed qla.value values in a single pass using a Common Table Expression (CTE), then join that precomputed data to your main query. Here's how it would look:

WITH precomputed_q_values AS (
    SELECT 
        qa.id AS qa_id,
        -- Map each q.name to its corresponding alias
        MAX(CASE WHEN q.name = 'Some name for tableS' THEN qla.value END) AS "Some alias for tableS",
        MAX(CASE WHEN q.name = 'Other name for tableS' THEN qla.value END) AS "Other alias for tableS",
        MAX(CASE WHEN q.name = 'Some name for tableA' THEN qla.value END) AS "Some alias for tableA",
        MAX(CASE WHEN q.name = 'Other Value for tableA' THEN qla.value END) AS "Other alias for tableA"
        -- Add more CASE statements here for your remaining q.name values
    FROM tableQ q
    INNER JOIN tableQL ql ON ql.q_id = q.id
    INNER JOIN tableQLA qla ON qla.ql_id = ql.id
    INNER JOIN tableQa qa ON qla.qa_id = qa.id
    WHERE ql.enabled = TRUE AND ql.archived = FALSE
    GROUP BY qa.id
)

SELECT 
    tableAa.name AS Aa_Name,
    tableAu.name AS Au_Name,
    tableA.code AS A_Code,
    -- Pull values from the precomputed CTE, joined to tableS's qa_id
    qs."Some alias for tableS",
    qs."Other alias for tableS",
    -- Pull values joined to tableA's qa_id
    qa_vals."Some alias for tableA",
    qa_vals."Other alias for tableA"
    -- Add other precomputed columns here
FROM tableS
LEFT JOIN tableA ON tableS.a_id = tableA.id
LEFT JOIN tableAa ON tableA.aa_id = tableAa.id
LEFT JOIN tableAu ON tableA.au_id = tableAu.id
-- Join the precomputed data twice: once for tableS's qa_id, once for tableA's
LEFT JOIN precomputed_q_values qs ON qs.qa_id = tableS.qa_id
LEFT JOIN precomputed_q_values qa_vals ON qa_vals.qa_id = tableA.qa_id
WHERE -- Your existing WHERE conditions
ORDER BY -- Your existing ORDER BY conditions

Why This Works #

• The CTE runs once total instead of once per row, doing all the table joins and filtering in a single pass.
• Conditional aggregation (MAX(CASE ...)) groups all values by qa.id, so each qa_id has all its corresponding q.name values ready to go.
• We join the precomputed CTE twice (once for tableS.qa_id, once for tableA.qa_id) to pull in the correct values for each context.

Bonus Performance Tips #

To make this even faster, add these indexes if you don't have them already:

• A composite index on tableQL (q_id, enabled, archived) to speed up filtering and joining to tableQ.
• Indexes on tableQLA (ql_id, qa_id) and tableQa (id) to optimize the joins between those tables.
• An index on tableQ (name) to speed up the q.name = '...' filters.

内容的提问来源于stack exchange，提问作者DeLaFuente