Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bpaLx-0002Yj-2H for pgsql-performance@arkaria.postgresql.org; Thu, 29 Sep 2016 12:22:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bpaLw-0002l6-LF for pgsql-performance@arkaria.postgresql.org; Thu, 29 Sep 2016 12:22:16 +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 ) id 1bpaKH-0000qj-QF for pgsql-performance@postgresql.org; Thu, 29 Sep 2016 12:20:33 +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 ) id 1bpaKE-000692-CY for pgsql-performance@postgresql.org; Thu, 29 Sep 2016 12:20:33 +0000 Received: from postfix03.mail.de (postfix03.bt.mail.de [10.0.121.127]) by shout02.mail.de (Postfix) with ESMTP id EFAE15FE14; Thu, 29 Sep 2016 14:20:27 +0200 (CEST) Received: from smtp03.mail.de (smtp03.bt.mail.de [10.0.121.213]) by postfix03.mail.de (Postfix) with ESMTP id D924840131; Thu, 29 Sep 2016 14:20:27 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=mail.de; s=mail201212; t=1475151627; bh=R2aHD4y0ZX/FSOg/ZGOwYko8ePueJ2sEwjnaNM4N7+c=; h=Subject:To:References:From:Date:In-Reply-To:From; b=iV6VZzwNBNp80/NznPjW5K+QDSAfqveQTlDr5//KrjfdmvAGdEbbY+gfScA9kjbQq TdztBRI1O9usdY5fgPERyL9K3IaiYYoOSlvAjM6YTZ0euikfOapDIskv7kqXLR/eWW Mj/PDenCbKM2X+Q5mtRrylHkVFEA+Wvbh3l7UYjY= 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 smtp03.mail.de (Postfix) with ESMTPSA id 9DCC64004F; Thu, 29 Sep 2016 14:20:27 +0200 (CEST) Subject: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause To: Pavel Stehule , pgsql-performance@postgresql.org References: <97dbd6f9-a289-94d1-325f-997df87c672a@mail.de> <0e86e399-61dc-8e78-415e-c83db9592a80@mail.de> From: "Sven R. Kunze" Message-ID: <8ccdd027-f4a0-2e0d-1e26-2a403fed8d17@mail.de> Date: Thu, 29 Sep 2016 14:20:31 +0200 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------1BD63E4EC240A0C0D043598F" 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: 3051 X-purgate-ID: 154282::1475151627-0000085D-2C8556D8/0/0 X-Pg-Spam-Score: -5.7 (-----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------1BD63E4EC240A0C0D043598F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 23.09.2016 11:00, Pavel Stehule wrote: > 2016-09-23 8:35 GMT+02:00 Sven R. Kunze >: > > I was wondering: would it be possible for PostgreSQL to rewrite > the query to generate the UNION (or subquery plan if it's also > fast) on it's own? > > > It depends on real data. On your specific data the UNION variant is > pretty fast, on different set, the UNION can be pretty slow. It is > related to difficult OR predicate estimation. I figure that the UNION is fast if the sub-results are small (which they are in our case). On the contrary, when they are huge, the OUTER JOIN variant might be preferable. Is there something I can do to help here? Or do you think it's naturally application-dependent and thus should be solved with application logic just as we did? Cheers, Sven --------------1BD63E4EC240A0C0D043598F Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit
On 23.09.2016 11:00, Pavel Stehule wrote:
2016-09-23 8:35 GMT+02:00 Sven R. Kunze <srkunze@mail.de>:
I was wondering: would it be possible for PostgreSQL to rewrite the query to generate the UNION (or subquery plan if it's also fast) on it's own?

It depends on real data. On your specific data the UNION variant is pretty fast, on different set, the UNION can be pretty slow. It is related to difficult OR predicate estimation.

I figure that the UNION is fast if the sub-results are small (which they are in our case). On the contrary, when they are huge, the OUTER JOIN variant might be preferable.


Is there something I can do to help here?

Or do you think it's naturally application-dependent and thus should be solved with application logic just as we did?


Cheers,
Sven
--------------1BD63E4EC240A0C0D043598F--