Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause

> 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(at)mail(dot)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.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Regards,
Madusudanan.B.N <http://madusudanan.com>