使用 OR 語句進行字元串搜尋非常慢

我有以下表格：

product, product_stock, 和product_offer

在查找 product_stock 中可用或目前提供的產品時，我需要優化搜尋。首先，當我添加一個子計劃時，事情看起來還不錯

explain select * from product p
where 
  p.product_number like '%T%'
and 
 p.id in 
     (select c.product_id from product_stock c where c.quantity > 0)

這給了我以下回饋：

Gather  (cost=1000.42..36876.20 rows=22531 width=53)

這對於超過 1m 條記錄的表非常有用

但是，當我添加如下 OR 語句時：

explain select * from product p
where 
  (p.product_number like '%T%')
and 
 (p.id in 
     (select c.product_id from product_stock c where c.quantity > 0)
  or p.id in 
     (select product_id from product_offer where now() between offer_start and offer_end) 
 )

這導致查詢非常慢，如下所示：

Gather  (cost=313039.34..166666168.02 rows=31023 width=53)
 Workers Planned: 2
       ->  Parallel Bitmap Heap Scan on product b  (cost=312039.34..166662065.72 rows=12926 width=53)
           Recheck Cond: ((product_number)::text ~~ '%T%'::text)
           Filter: ((SubPlan 1) OR (hashed SubPlan 2))
           ->  Bitmap Index Scan on product_trgm_gin  (cost=0.00..310574.23 rows=41364 width=0)
               Index Cond: ((product_number)::text ~~ '%T%'::text)
           SubPlan 1
             ->  Materialize  (cost=0.00..17907.26 rows=558188 width=8)
                 ->  Seq Scan on product_stock c  (cost=0.00..12935.32 rows=558188 width=8)
                          Filter: (quantity > 0)
           SubPlan 2
             ->  Seq Scan on product_offer (cost=0.00..1308.68 rows=59468 width=4)

擺脫這些愚蠢的子查詢並使用UNION而不是OR：

select p.* from product p
  join product_stock c on p.id = c.product_id
where c.quantity > 0
 and p.product_number like '%T%'
union
select p.* from product p
  join product_offer o on p.id = o.product_id
where now() between o.offer_start and o.offer_end
 and p.product_number like '%T%';

引用自：https://dba.stackexchange.com/questions/281573