查询friend表：UNION与OR语句的性能差异及推荐合理性确认

Let's break this down clearly based on your table structure and actual performance results.

First, recap your setup: you have a public.friend table storing unique bidirectional friend relationships with the rule uid1 < uid2 to avoid duplicates, plus separate BTREE indexes on uid1 and uid2. You tested two queries to find all friends of uid=2 and found Sql2 (using OR) performs better—but someone recommended using Sql1 (using UNION).

How Each Query Works #

• Sql1 (UNION approach): This runs two separate index scans: one on index_uid1 for rows where uid1=2, another on index_uid2 for rows where uid2=2. Then it uses UNION to combine results, which automatically removes duplicates. But here's the catch: your table's uid1 < uid2 guarantee means there's zero overlap between the two result sets (a row can't have both uid1=2 and uid2=2). That deduplication step is completely unnecessary overhead.
• Sql2 (OR approach): Modern PostgreSQL optimizers are smart enough to handle OR conditions across two indexed columns efficiently. It will typically perform a bitmap index scan (combining the two indexes) or merge the results of two index scans directly, skipping the redundant deduplication step. That's exactly why you're seeing better performance here—it's doing less work while getting the same correct results.

When Would the UNION Recommendation Make Sense? #

The only scenario where UNION might be preferable is if you're using an extremely old database version where the optimizer couldn't handle OR across indexed columns well (e.g., falling back to a full table scan instead of using the indexes). But in current versions of PostgreSQL (and most modern relational databases), the optimizer handles this OR case perfectly.

Final Verdict #

The recommendation to use Sql1 is not reasonable for your current setup. Your Sql2 is more efficient, cleaner, and leverages modern database optimization effectively.

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