From: Sven R. Kunze <srkunze@mail.de>
To: pgsql-performance@postgresql.org
Subject: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause
Date: Thu, 29 Sep 2016 20:48:01 +0200
Message-ID: <a0cde040-44f8-ac0f-c87a-932d2901702e@mail.de> (raw)
In-Reply-To: <CAMkU=1xkOSS6V=K4w=pcuUmV5Yz81=va6BpZeMyknGS4Sx9woA@mail.gmail.com>
References: <97dbd6f9-a289-94d1-325f-997df87c672a@mail.de>
<fc49b4e6-8912-2a8d-4f58-7d1ed455cb49@ed.ac.uk>
<0e86e399-61dc-8e78-415e-c83db9592a80@mail.de>
<CAMkU=1xkOSS6V=K4w=pcuUmV5Yz81=va6BpZeMyknGS4Sx9woA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: srkunze@mail.de
Subject: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause
In-Reply-To: <a0cde040-44f8-ac0f-c87a-932d2901702e@mail.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox