-----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Igor Neyman Sent: Thursday, September 22, 2016 10:36 AM To: Sven R. Kunze <srkunze@xxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause -----Original Message----- From: Igor Neyman Sent: Thursday, September 22, 2016 10:33 AM To: 'Sven R. Kunze' <srkunze@xxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx Subject: RE: Multiple-Table-Spanning Joins with ORs in WHERE Clause -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Sven R. Kunze Sent: Thursday, September 22, 2016 9:25 AM To: pgsql-performance@xxxxxxxxxxxxxx Subject: 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. ______________________________________________________________________________________________ Another option to try:: SELECT DISTINCT <fields of big_table> FROM "big_table" LEFT OUTER JOIN "table_a" ON ("big_table"."id" = "table_a"."big_table_id" AND "table_a"."item_id" IN (<handful of items>)) LEFT OUTER JOIN "table_b" ON ("big_table"."id" = "table_b"."big_table_id" AND "table_b"."item_id" IN (<handful of items>)); Regards, Igor Neyman _______________________________________________________________________________________________________ Please disregard this last suggestion, it'll not produce required results. Solution using UNION should work. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
