String search is very slow with OR statement I have the following tables:
product, product_stock, and product_offer
I need to optimize the search when looking for a product that is available in product_stock or that is currently on offer. First, when I add one subplan, things look ok
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 &gt; 0)

This gives me the following feedback:
Gather  (cost=1000.42..36876.20 rows=22531 width=53)

Which is very good for a table over 1m records
However, when I add an OR statement as following:
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 &gt; 0)
   or p.id in 
      (select product_id from product_offer where now() between offer_start and offer_end) 
  )

This results in a very slow query as following:
Gather  (cost=313039.34..166666168.02 rows=31023 width=53)
  Workers Planned: 2
        -&gt;  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))
            -&gt;  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
              -&gt;  Materialize  (cost=0.00..17907.26 rows=558188 width=8)
                  -&gt;  Seq Scan on product_stock c  (cost=0.00..12935.32 rows=558188 width=8)
                           Filter: (quantity &gt; 0)
            SubPlan 2
              -&gt;  Seq Scan on product_offer (cost=0.00..1308.68 rows=59468 width=4)
Get rid of these silly subqueries and use UNION rather than OR:
select p.* from product p
   join product_stock c on p.id = c.product_id
where c.quantity &gt; 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%';