Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause
От | Igor Neyman |
---|---|
Тема | Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause |
Дата | |
Msg-id | CY4PR07MB2872AB33EDC46AF17F248D75DAC90@CY4PR07MB2872.namprd07.prod.outlook.com обсуждение исходный текст |
Ответ на | Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause (Igor Neyman <ineyman@perceptron.com>) |
Список | pgsql-performance |
-----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Igor Neyman Sent: Thursday, September 22, 2016 10:36 AM To: Sven R. Kunze <srkunze@mail.de>; pgsql-performance@postgresql.org Subject: Re: [PERFORM] 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@mail.de>; pgsql-performance@postgresql.org Subject: RE: [PERFORM] Multiple-Table-Spanning Joins with ORs in WHERE Clause -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Sven R. Kunze Sent: Thursday, September 22, 2016 9:25 AM To: pgsql-performance@postgresql.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 applicationlogic: 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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Igor NeymanДата:
Сообщение: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause
Следующее
От: Jeff JanesДата:
Сообщение: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause