Does PostgreSQL rewrite JOINs with OR's into UNIONs? (Abbreviated) table summary:

-- Table cases:
     id                  SERIAL       PRIMARY KEY,
     application_number  VARCHAR(30)  NOT NULL,
     publication_number  VARCHAR(30)  NOT NULL,

-- Table patents:
     case_id                 INTEGER      PRIMARY KEY,  -- FK to cases(id)
     pct_application_number  VARCHAR(30)  NOT NULL,
     pct_publication_number  VARCHAR(30)  NOT NULL,

-- All character columns have working trigram indexes:
     CREATE INDEX cases_application_number_trgm_idx ON cases
         USING GIN (application_number gin_trgm_ops);
         -- (etc)


The following query is slow (~200ms) because it doesn't use the indexes:

SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%' OR p.pct_application_number ILIKE '%1234%'


The following queries are all fast (1-2ms):

-- AND instead of OR
WHERE  c.application_number ILIKE '%1234%' AND p.pct_application_number ILIKE '%1234%'

-- OR, but only table "cases"
WHERE  c.application_number ILIKE '%1234%' OR c.publication_number ILIKE '%1234%'

-- OR, but only table "patents"
WHERE  p.pct_application_number ILIKE '%1234%' OR p.pct_publication_number ILIKE '%1234%'

-- Simulating the OR with a UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%'
 UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  p.pct_application_number ILIKE '%1234%'


Here is the EXPLAIN ANALYZE output for the slow query:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=2329.80..10590.54 rows=7 width=4) (actual time=54.951..186.713 rows=35 loops=1)
   Hash Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   -&gt;  Seq Scan on cases c  (cost=0.00..4981.99 rows=142099 width=12) (actual time=0.011..32.875 rows=142099 loops=1)
   -&gt;  Hash  (cost=1142.58..1142.58 rows=68258 width=11) (actual time=31.105..31.105 rows=68258 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2473kB
         -&gt;  Seq Scan on patents p  (cost=0.00..1142.58 rows=68258 width=11) (actual time=0.019..11.995 rows=68258 loops=1)
 Planning time: 1.875 ms
 Execution time: 186.780 ms
(10 rows)


The query as posted here is heavily reduced to illustrate the problem. The actual query is more complex and involves a text search in six (or more) columns in five (or more) tables, with about 10 output columns. I guess I could rewrite all that as series of queries and connect them into a huge UNION... is there a better way to deal with this problem?



Adding query plan with enable_seqscan disabled (as requested):

                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.71..18767.92 rows=7 width=4) (actual time=4.809..150.368 rows=35 loops=1)
   Merge Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   -&gt;  Index Scan using cases_pkey on cases c  (cost=0.42..14942.96 rows=142099 width=12) (actual time=0.004..32.695 rows=142097 loops=1)
   -&gt;  Index Scan using patents_pkey on patents p  (cost=0.29..2275.63 rows=68258 width=11) (actual time=0.003..11.942 rows=68258 loops=1)
 Planning time: 1.007 ms
 Execution time: 150.399 ms
(8 rows)
I'm afraid there is no good solution for you now, other than rewrite into a UNION (or denormalize/refactor the data).

There is a proposal to add automatic conversion of ORs to UNIONs but it needs more testing and reviews to get it into v11 of PostgreSQL, but I have verified that it does work for a case just like yours.