合并两个JOIN查询结果并去重，UNION执行耗时过长求助

Hey there! Let's dig into why your UNION query is running slow and fix it. The core issue here is that UNION forces the database to sort the entire combined result set to remove duplicates—this gets really expensive when dealing with large datasets. Here are practical, tested solutions to speed things up:

First, make sure your JOIN operations are as fast as possible. Missing indexes on the join columns are often the root cause of slow queries. Add these indexes:

-- For Query 1's JOIN
CREATE INDEX idx_t1_c1 ON t1(c1);
CREATE INDEX idx_t2_c1 ON t2(c1);

-- For Query 2's JOIN
CREATE INDEX idx_t1_c2 ON t1(c2);
CREATE INDEX idx_t3_c2 ON t3(c2);

Indexes let the database find matching rows quickly instead of scanning entire tables. This will speed up each individual query, which directly improves the overall UNION performance.

UNION does two things: concatenates results AND removes duplicates (via sorting). UNION ALL skips the sorting step entirely, making it way faster. We can handle deduplication separately, which is often more efficient:

Option A: Use DISTINCT with UNION ALL #

SELECT DISTINCT combined.*
FROM (
    SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1
    UNION ALL
    SELECT t1.* FROM t1 JOIN t3 ON t1.c2 = t3.c2
) AS combined;

Option B: Row Numbering (Better for Tables with a Primary Key) #

If t1 has a unique primary key (say t1_id), use row numbering to keep only one copy of each row:

SELECT *
FROM (
    SELECT 
        t1.*,
        ROW_NUMBER() OVER (PARTITION BY t1.t1_id ORDER BY t1.t1_id) AS rn
    FROM (
        SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1
        UNION ALL
        SELECT t1.* FROM t1 JOIN t3 ON t1.c2 = t3.c2
    ) AS t1
) AS ranked
WHERE rn = 1;

This avoids a full sort of the combined dataset and targets duplicates directly using the primary key.

Instead of combining two separate JOIN results, we can check for matching rows in t2 or t3 directly in a single query using EXISTS clauses. This is often much faster because it doesn't pull all joined data—just checks for existence:

SELECT t1.*
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.c1 = t2.c1)
   OR EXISTS (SELECT 1 FROM t3 WHERE t1.c2 = t3.c2);

This gives you the exact same result as your original UNION query, but the database can optimize it better (especially with proper indexes).

Run EXPLAIN before your query to see exactly where the time is being spent:

EXPLAIN
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1
UNION
SELECT t1.* FROM t1 JOIN t3 ON t1.c2 = t3.c2;

Look for:

• Full table scans (instead of index scans)
• Large sort operations (labeled something like Sort or Unique)
• High row counts in intermediate steps
  This will tell you if the problem is with the JOINs, the UNION sort, or something else entirely.

If you run this query often, a materialized view precomputes the result and stores it physically. This turns your slow query into a fast lookup. For example, in PostgreSQL:

CREATE MATERIALIZED VIEW mv_t1_combined AS
SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1
UNION
SELECT t1.* FROM t1 JOIN t3 ON t1.c2 = t3.c2;

Then query it like this:

SELECT * FROM mv_t1_combined;

Just remember to refresh the view periodically (e.g., REFRESH MATERIALIZED VIEW mv_t1_combined;) if your underlying data changes.

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