PostgreSQL中带OR条件的查询比拆分查询慢的优化问题

问题背景 #

有两张各约200万行的表main_table和other_table，用户访问main_table的权限判断逻辑为：

• 用户user_id与main_table.user_id字段匹配
• 或other_table中存在read_acl（已建GIN索引的数组字段）包含该用户user_id的条目

当前使用带OR条件的查询速度极慢，执行计划显示PostgreSQL对main_table执行了全索引扫描；但将OR拆分为两个独立查询时，速度大幅提升，需要优化该查询。

原查询及执行计划 #

select *
from main_table
where user_id = 123 or exists (select * from other_table f 
                               where main_table.main_table_id = f.main_table_id 
                               and '{123}' && read_acl)
order by main_table_id limit 10;

执行计划：

Limit  (cost=0.43..170.97 rows=10 width=2590) (actual time=172.734..389.819 rows=10 loops=1)
   ->  Index Scan using main_table_pk on main_table  (cost=0.43..14158795.44 rows=830198 width=2590) (actual time=172.733..389.811 rows=10 loops=1)
         Filter: ((user_id = 123) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
         Rows Removed by Filter: 776709
         SubPlan 1
           ->  Index Scan using other_table_main_table_id_idx on other_table f  (cost=0.43..8.45 rows=1 width=0) (never executed)
                 Index Cond: (main_table.main_table_id = main_table_id)
                 Filter: ('{123}'::text[] && read_acl)
         SubPlan 2
           ->  Bitmap Heap Scan on other_table f_1  (cost=1678.58..2992.04 rows=333 width=8) (actual time=9.413..9.432 rows=12 loops=1)
                 Recheck Cond: ('{123}'::text[] && read_acl)
                 Heap Blocks: exact=12
                 ->  Bitmap Index Scan on other_table_read_acl  (cost=0.00..1678.50 rows=333 width=0) (actual time=9.401..9.401 rows=12 loops=1)
                       Index Cond: ('{123}'::text[] && read_acl)
 Planning Time: 0.395 ms
 Execution Time: 389.877 ms
(16 rows)

拆分后的独立查询及执行计划 #

查询1（匹配user_id） #

select *
from main_table
where user_id = 123
order by main_table_id limit 10;

执行计划：

Limit  (cost=482.27..482.29 rows=10 width=2590) (actual time=0.039..0.040 rows=8 loops=1)
   ->  Sort  (cost=482.27..482.58 rows=126 width=2590) (actual time=0.038..0.039 rows=8 loops=1)
         Sort Key: main_table_id
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on main_table  (cost=5.40..479.54 rows=126 width=2590) (actual time=0.020..0.031 rows=8 loops=1)
               Recheck Cond: (user_id = 123)
               Heap Blocks: exact=8
               ->  Bitmap Index Scan on test500  (cost=0.00..5.37 rows=126 width=0) (actual time=0.015..0.015 rows=8 loops=1)
                     Index Cond: (user_id = 123)
 Planning Time: 0.130 ms
 Execution Time: 0.066 ms
(11 rows)

查询2（匹配read_acl权限） #

select *
from main_table
where exists (select * from other_table f 
              where main_table.main_table_id = f.main_table_id 
              and '{123}' && read_acl)
order by main_table_id limit 10;

执行计划：

Limit  (cost=5771.59..5771.62 rows=10 width=2590) (actual time=8.083..8.086 rows=10 loops=1)
   ->  Sort  (cost=5771.59..5772.42 rows=333 width=2590) (actual time=8.082..8.083 rows=10 loops=1)
         Sort Key: main_table.main_table_id
         Sort Method: quicksort  Memory: 26kB
         ->  Nested Loop  (cost=2985.30..5764.40 rows=333 width=2590) (actual time=8.018..8.072 rows=12 loops=1)
               ->  HashAggregate  (cost=2984.88..2988.21 rows=333 width=8) (actual time=7.999..8.004 rows=12 loops=1)
                     Group Key: f.main_table_id
                     ->  Bitmap Heap Scan on other_table f  (cost=1670.58..2984.04 rows=333 width=8) (actual time=7.969..7.990 rows=12 loops=1)
                           Recheck Cond: ('{123}'::text[] && read_acl)
                           Heap Blocks: exact=12
                           ->  Bitmap Index Scan on other_table_read_acl  (cost=0.00..1670.50 rows=333 width=0) (actual time=7.957..7.958 rows=12 loops=1)
                                 Index Cond: ('{123}'::text[] && read_acl)
               ->  Index Scan using main_table_pk on main_table  (cost=0.43..8.34 rows=1 width=2590) (actual time=0.005..0.005 rows=1 loops=12)
                     Index Cond: (main_table_id = f.main_table_id)
 Planning Time: 0.431 ms
 Execution Time: 8.137 ms
(16 rows)

优化方案 #

1. 使用UNION ALL合并独立查询（推荐） #

利用两个独立查询的高效执行特性，用UNION ALL合并结果后排序取前10。如果业务上存在同一main_table_id同时满足两个条件的情况，需要替换为UNION去重（但UNION ALL性能更高）。

SELECT * FROM (
    -- 匹配user_id的结果
    SELECT * FROM main_table WHERE user_id = 123
    UNION ALL
    -- 匹配read_acl权限的结果
    SELECT * FROM main_table 
    WHERE EXISTS (
        SELECT 1 FROM other_table f 
        WHERE main_table.main_table_id = f.main_table_id 
        AND '{123}' && read_acl
    )
) AS combined_results
ORDER BY main_table_id LIMIT 10;

2. 改写EXISTS为IN引导优化器 #

将原查询的OR条件拆分为user_id匹配 + main_table_id在权限列表中，让优化器先预取所有有权限的main_table_id，再合并结果：

SELECT * FROM main_table
WHERE user_id = 123 
OR main_table_id IN (
    SELECT main_table_id FROM other_table WHERE '{123}' && read_acl
)
ORDER BY main_table_id LIMIT 10;

3. 更新统计信息 #

确保PostgreSQL的表统计信息是最新的，帮助优化器选择最优执行计划：

ANALYZE main_table;
ANALYZE other_table;

原查询慢的原因 #

PostgreSQL的查询优化器在处理OR条件时，无法同时高效利用main_table.user_id的索引和other_table.read_acl的GIN索引关联路径，最终选择了对main_table主键索引做全扫描，逐个过滤每行是否满足任一条件，导致扫描了近80万条无关数据（执行计划中Rows Removed by Filter: 776709）。而拆分后的查询分别利用了对应索引，避免了全扫描，因此性能大幅提升。

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