PostgreSQL slow performance using "or" operator

Question

Development team on my company built the following query:

select
   distinct c.customer_id
from table1 c
   join table2 l
      on c.customer_id = l.customer_id
   join table3 cal
      on c.customer_id = cal.customer_id
WHERE
   (l.customer_group_id = 'loyalty' and c.loyalty_number = '123456789')
   or
   (cal.account_id = '123456789' and cal.account_type  = 'loyalty')
;

That query is running on a non-production environment and is taking 2.5 minutes to complete. Explain analyze show me it is doing SEQ SCAN on all tables to complete the SQL.

If I comment any of the where clauses (or run a new query leaving only one of the where clauses), example 1:

   (l.customer_group_id = 'loyalty' and c.loyalty_number = '123456789')
-- or
-- (cal.account_id = '123456789' and cal.account_type  = 'loyalty')

or example 2:

-- (l.customer_group_id = 'loyalty' and c.loyalty_number = '123456789')
-- or
   (cal.account_id = '123456789' and cal.account_type  = 'loyalty')

The query completes in milisseconds. On that case, explain analyze shows me query planner used the indexes available to perform the operation.

I'm having a real hard time understanding why the operator "or" makes query planner not use the indexes at all. In any case the number of rows retrieved are 3. A lot of rows are discarded in the process.

If I replace the operator "or" for an "and" also makes the query complete in milliseconds.

What am I missing, conceptually or not, about the logic behind the "or" operator? I can't make sense or come up with a reason why this happens. Not sure if it is expected behavior or if there is something I can do about it.

Since one where clause completes with milliseconds, the other (running separately) also completes in milliseconds, why is it when they are placed together there is an absurd time of > 2 minutes to complete?

I'm providing more details about my environment below. Any comment will be appreciated. Thank you in advance.

-- Environment details
PostgreSQL running on AWS Amazon Aurora Serverless
Engine PostgreSQL 10.14

-- # of rows for each table
 1 - 67,904,804
 2 - 67,955,984
 3 - 67,902,549

-- Table sizes (without index or toast)
       table       |    size    |  value
-------------------+------------+---------
 1                 | TABLE SIZE | 8 GB
 2                 | TABLE SIZE | 10 GB
 3                 | TABLE SIZE | 7 GB

-- Explain plan for complete query, taking 2.5 min to complete
    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=12268978.36..12268978.39 rows=5 width=19) (actual time=150567.032..150567.104 rows=3 loops=1)
   ->  Sort  (cost=12268978.36..12268978.37 rows=5 width=19) (actual time=150567.032..150567.101 rows=3 loops=1)
         Sort Key: c.customer_id
         Sort Method: quicksort  Memory: 25kB
         ->  Gather  (cost=6171007.40..12268978.30 rows=5 width=19) (actual time=141341.630..150567.067 rows=3 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Hash Join  (cost=6170007.40..12267977.80 rows=2 width=19) (actual time=142581.256..149478.215 rows=1 loops=3)
                     Hash Cond: ((l.customer_id)::text = (c.customer_id)::text)
                     Join Filter: ((((l.customer_group_id)::text = 'loyalty'::text) AND ((c.loyalty_number)::text = '654654654'::text)) OR (((cal.account_id)::text = '654654654'::text) AND ((cal.account_type)::text = 'loyalty'::text)))
                     Rows Removed by Join Filter: 22611489
                     ->  Hash Join  (cost=2863005.22..7836194.25 rows=27605097 width=64) (actual time=48335.237..79764.899 rows=22611495 loops=3)
                           Hash Cond: ((cal.customer_id)::text = (l.customer_id)::text)
                           ->  Parallel Seq Scan on cal  (cost=0.00..1397382.48 rows=28291748 width=37) (actual time=0.014..4722.632 rows=22632059 loops=3)
                           ->  Hash  (cost=1581955.32..1581955.32 rows=66252232 width=27) (actual time=48310.717..48310.718 rows=67955982 loops=3)
                                 Buckets: 65536  Batches: 2048  Memory Usage: 2428kB
                                 ->  Seq Scan on l  (cost=0.00..1581955.32 rows=66252232 width=27) (actual time=0.028..13237.966 rows=67955982 loops=3)
                     ->  Hash  (cost=2008016.30..2008016.30 rows=67179830 width=29) (actual time=49635.414..49635.415 rows=67898462 loops=3)
                           Buckets: 65536  Batches: 2048  Memory Usage: 2492kB
                           ->  Seq Scan on c  (cost=0.00..2008016.30 rows=67179830 width=29) (actual time=0.040..15735.018 rows=67898462 loops=3)
 Planning time: 0.620 ms
 Execution time: 150568.156 ms
(22 rows)

Time: 150786.479 ms (02:30.786)


-- Explain plan when I comment one of the where clauses
  QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=32.11..32.13 rows=3 width=19) (actual time=0.101..0.103 rows=3 loops=1)
   ->  Sort  (cost=32.11..32.12 rows=3 width=19) (actual time=0.101..0.102 rows=3 loops=1)
         Sort Key: c.customer_id
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=1.70..32.09 rows=3 width=19) (actual time=0.049..0.096 rows=3 loops=1)
               Join Filter: ((c.customer_id)::text = (cal.customer_id)::text)
               ->  Nested Loop  (cost=1.14..30.24 rows=3 width=38) (actual time=0.035..0.060 rows=3 loops=1)
                     ->  Index Scan using idx on c (cost=0.57..16.47 rows=3 width=19) (actual time=0.015..0.018 rows=3 loops=1)
                           Index Cond: ((loyalty_number)::text = '654654654'::text)
                     ->  Index Only Scan using pkey on l  (cost=0.57..4.59 rows=1 width=19) (actual time=0.013..0.013 rows=1 loops=3)
                           Index Cond: ((customer_group_id = 'loyalty'::text) AND (customer_id = (c.customer_id)::text))
                           Heap Fetches: 0
               ->  Index Only Scan using idx on cal  (cost=0.57..0.60 rows=1 width=19) (actual time=0.011..0.011 rows=1 loops=3)
                     Index Cond: (customer_id = (l.customer_id)::text)
                     Heap Fetches: 0
 Planning time: 0.555 ms
 Execution time: 0.128 ms

Answer 1

A basic, 100 % equivalent rewrite of your "ugly-OR" query with UNION:

SELECT customer_id
FROM   table1 c
JOIN   table2 l   USING (customer_id)
JOIN   table3 cal USING (customer_id)
WHERE  l.customer_group_id = 'loyalty'
AND    c.loyalty_number = '123456789'
UNION
SELECT customer_id
FROM   table1 c
JOIN   table2 l   USING (customer_id)
JOIN   table3 cal USING (customer_id)
WHERE  cal.account_id = '123456789'
AND    cal.account_type  = 'loyalty'

Should already be substantially faster, as it can use indexes like your partial queries. See:

• Why is an OR statement slower than UNION?

Typically, we can optimize further. But we'd need actual table definitions. Most importantly PK and FK constraints, and whether the same customer_id can be present in one table and missing in another. (Or have multiple instances - but that's of secondary relevance for your DISTINCT query.)

Like, if any qualifying customer_id that exists in one of the three tables is guaranteed to exist in all three (one or multiple rows), we can simplify:

SELECT customer_id
FROM   table1 c
JOIN   table2 l   USING (customer_id)
WHERE  l.customer_group_id = 'loyalty'
AND    c.loyalty_number = '123456789'
UNION
SELECT customer_id
FROM   table3 cal
WHERE  cal.account_id = '123456789'
AND    cal.account_type  = 'loyalty';