Multiple-Table-Spanning Joins with ORs in WHERE Clause

From: "Sven R(dot) Kunze" <srkunze(at)mail(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Multiple-Table-Spanning Joins with ORs in WHERE Clause
Date: 2016-09-22 13:24:49
Message-ID: 97dbd6f9-a289-94d1-325f-997df87c672a@mail.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi pgsql-performance list,

what is the recommended way of doing **multiple-table-spanning joins
with ORs in the WHERE-clause**?

Until now, we've used the LEFT OUTER JOIN to filter big_table like so:

SELECT DISTINCT <fields of big_table>
FROM
"big_table"
LEFT OUTER JOIN "table_a" ON ("big_table"."id" =
"table_a"."big_table_id")
LEFT OUTER JOIN "table_b" ON ("big_table"."id" =
"table_b"."big_table_id")
WHERE
"table_a"."item_id" IN (<handful of items>)
OR
"table_b"."item_id" IN (<handful of items>);

However, this results in an awful slow plan (requiring to scan the
complete big_table which obviously isn't optimal).
So, we decided (at least for now) to split up the query into two
separate ones and merge/de-duplicate the result with application logic:

SELECT <fields of big_table>
FROM
"big_table" INNER JOIN "table_a" ON ("big_table"."id" =
"table_a"."big_table_id")
WHERE
"table_a"."item_id" IN (<handful of items>);

SELECT <fields of big_table>
FROM
"big_table" INNER JOIN "table_b" ON ("big_table"."id" =
"table_b"."big_table_id")
WHERE
"table_b"."item_id" IN (<handful of items>);

As you can imagine we would be very glad to solve this issue with a
single query and without having to re-code existing logic of PostgreSQL.
But how?

Best,
Sven

PS: if you require EXPLAIN ANALYZE, I can post them as well.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Madusudanan.B.N 2016-09-22 13:37:17 Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause
Previous Message Knels, Udo 2016-09-22 12:39:30 Re: Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher