From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | "Sven R(dot) Kunze" <srkunze(at)mail(dot)de>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause |
Date: | 2016-09-22 14:32:53 |
Message-ID: | CY4PR07MB2872B533247E5C8753E11956DAC90@CY4PR07MB2872.namprd07.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Sven R. Kunze
Sent: Thursday, September 22, 2016 9:25 AM
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause
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.
______________________________________________________________________________________________
What about:
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>)
UNION
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>);
Regards,
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-09-22 14:35:30 | Re: Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher |
Previous Message | Igor Neyman | 2016-09-22 13:38:52 | Re: Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher |