Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bpgNP-0005Yr-SD for pgsql-performance@arkaria.postgresql.org; Thu, 29 Sep 2016 18:48:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bpgNP-0005aK-Eg for pgsql-performance@arkaria.postgresql.org; Thu, 29 Sep 2016 18:48:11 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bpgNO-0005Y3-Du for pgsql-performance@postgresql.org; Thu, 29 Sep 2016 18:48:10 +0000 Received: from shout02.mail.de ([2001:868:100:600::217]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bpgNL-0001Xp-2V for pgsql-performance@postgresql.org; Thu, 29 Sep 2016 18:48:09 +0000 Received: from postfix01.mail.de (postfix01.bt.mail.de [10.0.121.125]) by shout02.mail.de (Postfix) with ESMTP id 11B505FF26 for ; Thu, 29 Sep 2016 20:48:03 +0200 (CEST) Received: from smtp01.mail.de (smtp01.bt.mail.de [10.0.121.211]) by postfix01.mail.de (Postfix) with ESMTP id EE9D54010B for ; Thu, 29 Sep 2016 20:48:02 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=mail.de; s=mail201212; t=1475174883; bh=rg9CBYJvXSKufnZie19MXRIJn9iohXr0A5DMrszg+nE=; h=Subject:To:References:From:Date:In-Reply-To:From; b=zOGI8/38HqOYk27FLHlrDSr+dsVDPh9OfaJgvBiTv21mWSemt13Znt9DWArxOSJ6Z XZOZ9s8YALwYvReFpADyIc+pcMGigBMGCXRtMkN9JPfACVIqZ7Zcc/qzOBtaFPbay9 Q5KVdbXEcyzvQzUebF6GA0TVbrfsoC0RB/b3jgE4= 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 7285F4009D for ; Thu, 29 Sep 2016 20:48:02 +0200 (CEST) Subject: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause To: 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: Date: Thu, 29 Sep 2016 20:48:01 +0200 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------EA787CD234B1AFCE17CD3D0B" 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: 10105 X-purgate-ID: 154282::1475174882-00000833-1042448B/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. --------------EA787CD234B1AFCE17CD3D0B Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Hi Jeff, On 29.09.2016 20:03, Jeff Janes wrote: > I don't know what the subquery plan is, I don't see references to that > in the email chain. Lutz posted the following solution: SELECT * FROM big_table WHERE id in (SELECT big_table_id FROM table_a WHERE "table_a"."item_id" IN ()) OR id in (SELECT big_table_id FROM table_a WHERE "table_b"."item_id" IN ()) > I don't believe that current versions of PostgreSQL are capable of > rewriting the plan in the style of a union. It is not just a matter > of tweaking the cost estimates, it simply never considers such a plan > in the first place given the text of your query. That's okay and that's why I am asking here. :) > Perhaps some future version of PostgreSQL could do so, but my gut > feeling is that that is not very likely. It would take a lot of work, > would risk breaking or slowing down other things, and is probably too > much of a niche issue to attract a lot of interest. I don't hope so; in business and reports/stats applications there is a lot of room for this. Why do you think that OR-ing several tables is a niche issue? I can at least name 3 different projects (from 3 different domains) where combining 3 or more tables with OR is relevant and should be reasonably fast. Most domains that could benefit would probably have star-like schemas. So, big_table corresponds to the center of the star, whereas the rays correspond to various (even dynamic) extensions to the base data structure. > Why not just use the union? Sure that would work in this particular case. However, this thread actually sought a general answer to "how to OR more than two tables". > Are you using a framework which generates the query automatically and > you have no control over it? We use a framework and we can use the UNION if we want to. > Or do you just think it is ugly or fragile for some other reason? I don't think it's ugly or fragile. I am just used to the fact that **if it's equivalent** then PostgreSQL can figure it out (without constant supervision from application developers). So, it's just a matter of inconvenience. ;) > Perhaps moving the union from the outside to the inside would be more > suitable? That way teh select list is only specified once, and if you > AND more clauses into the WHERE condition they also only need to be > specified once. > > SELECT * FROM big_table > WHERE > id in (SELECT big_table_id FROM table_a WHERE "table_a"."item_id" > IN () union > SELECT big_table_id FROM table_a WHERE "table_b"."item_id" IN > () > ); Yet another solution I guess, so thanks a lot. :) This multitude of solution also shows that applications developers might be overwhelmed by choosing the most appropriate AND most long-lasting one. Because what I take from the discussion is that a UNION might be appropriate right now but that could change in the future even for the very same use-case at hand. Cheers, Sven --------------EA787CD234B1AFCE17CD3D0B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Hi Jeff,

On 29.09.2016 20:03, Jeff Janes wrote:
I don't know what the subquery plan is, I don't see references to that in the email chain.

Lutz posted the following solution:

SELECT * FROM big_table
WHERE
     id in (SELECT big_table_id FROM table_a WHERE "table_a"."item_id" IN (<handful of items>))
    OR
     id in (SELECT big_table_id FROM table_a WHERE "table_b"."item_id" IN (<handful of items>))

I don't believe that current versions of PostgreSQL are capable of rewriting the plan in the style of a union.  It is not just a matter of tweaking the cost estimates, it simply never considers such a plan in the first place given the text of your query.

That's okay and that's why I am asking here. :)

Perhaps some future version of PostgreSQL could do so, but my gut feeling is that that is not very likely.  It would take a lot of work, would risk breaking or slowing down other things, and is probably too much of a niche issue to attract a lot of interest.

I don't hope so; in business and reports/stats applications there is a lot of room for this.

Why do you think that OR-ing several tables is a niche issue? I can at least name 3 different projects (from 3 different domains) where combining 3 or more tables with OR is relevant and should be reasonably fast.

Most domains that could benefit would probably have star-like schemas. So, big_table corresponds to the center of the star, whereas the rays correspond to various (even dynamic) extensions to the base data structure.

Why not just use the union?

Sure that would work in this particular case. However, this thread actually sought a general answer to "how to OR more than two tables".

Are you using a framework which generates the query automatically and you have no control over it?

We use a framework and we can use the UNION if we want to.

Or do you just think it is ugly or fragile for some other reason?

I don't think it's ugly or fragile. I am just used to the fact that **if it's equivalent** then PostgreSQL can figure it out (without constant supervision from application developers).

So, it's just a matter of inconvenience. ;)

Perhaps moving the union from the outside to the inside would be more suitable?  That way teh select list is only specified once, and if you AND more clauses into the WHERE condition they also only need to be specified once.

SELECT * FROM big_table
WHERE
     id in (SELECT big_table_id FROM table_a WHERE "table_a"."item_id" IN (<handful of items>) union 
             SELECT big_table_id FROM table_a WHERE "table_b"."item_id" IN (<handful of items>)
      );

Yet another solution I guess, so thanks a lot. :)

This multitude of solution also shows that applications developers might be overwhelmed by choosing the most appropriate AND most long-lasting one. Because what I take from the discussion is that a UNION might be appropriate right now but that could change in the future even for the very same use-case at hand.

Cheers,
Sven
--------------EA787CD234B1AFCE17CD3D0B--