> 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@mail.de> 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.
