Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54406=pgsql-performance-ar@postgresql.org>)
	id 1bn3zl-0001aV-IC
	for pgsql-performance@arkaria.postgresql.org; Thu, 22 Sep 2016 13:24:58 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54406=pgsql-performance-ar@postgresql.org>)
	id 1bn3zk-0002C3-VD
	for pgsql-performance@arkaria.postgresql.org; Thu, 22 Sep 2016 13:24:57 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <srkunze@mail.de>)
	id 1bn3zj-0002BH-QV
	for pgsql-performance@postgresql.org; Thu, 22 Sep 2016 13:24:56 +0000
Received: from shout02.mail.de ([2001:868:100:600::217])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <srkunze@mail.de>)
	id 1bn3zf-0004wL-4w
	for pgsql-performance@postgresql.org; Thu, 22 Sep 2016 13:24:55 +0000
Received: from postfix01.mail.de (postfix01.bt.mail.de [10.0.121.125])
	by shout02.mail.de (Postfix) with ESMTP id 6F44863354
	for <pgsql-performance@postgresql.org>;
 Thu, 22 Sep 2016 15:24:48 +0200 (CEST)
Received: from smtp01.mail.de (smtp01.bt.mail.de [10.0.121.211])
	by postfix01.mail.de (Postfix) with ESMTP id 59089506D2
	for <pgsql-performance@postgresql.org>;
 Thu, 22 Sep 2016 15:24:48 +0200 (CEST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=mail.de;
	s=mail201212; t=1474550688;
	bh=9iKyY8zGZUvrkuO7UeIu2NMz47BDsHMjL6isTtW2el4=;
	h=To:From:Subject:Date:From;
	b=TzEPRiitLSqewZrctorEfJyU7vRIkGPT/yYRpS3eus5ikv2eAVFpHPWahUA8bbpDE
	 EBE9iQJpWl2M7qYE4OVQYuzTnptpGi8TpYwAKlC/9upItsNAQsz6tpEvDnimMnCx11
	 mEksI48HBcI2vKR2l2JkijtJRr4j06/F9h4QXTno=
Received: from [127.0.0.1] (localhost [127.0.0.1])
	(using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by smtp01.mail.de (Postfix) with ESMTPSA id 2D55F403B2
	for <pgsql-performance@postgresql.org>;
 Thu, 22 Sep 2016 15:24:48 +0200 (CEST)
To: pgsql-performance@postgresql.org
From: "Sven R. Kunze" <srkunze@mail.de>
Subject: Multiple-Table-Spanning Joins with ORs in WHERE Clause
Message-ID: <97dbd6f9-a289-94d1-325f-997df87c672a@mail.de>
Date: Thu, 22 Sep 2016 15:24:49 +0200
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
X-purgate: clean
X-purgate: This mail is considered clean (visit http://www.eleven.de for
 further information)
X-purgate-type: clean
X-purgate-Ad: Categorized by eleven eXpurgate (R) http://www.eleven.de
X-purgate: This mail is considered clean (visit http://www.eleven.de for
 further information)
X-purgate: clean
X-purgate-size: 1445
X-purgate-ID: 154282::1474550688-00000833-13CC2C28/0/0
X-Pg-Spam-Score: -5.8 (-----)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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