> However, this results in an awful slow plan (requiring to scan the complete big_table which obviously isn't optimal)
You mean to say there is a sequential scan ? An explain would be helpful. Are there indexes on the provided where clauses.
Postgres can do a Bitmap heap scan to combine indexes, there is no need to fire two separate queries.
On Thu, Sep 22, 2016 at 6:54 PM, Sven R. Kunze <srkunze@xxxxxxx> wrote:
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Regards,
Madusudanan.B.N
Madusudanan.B.N
