Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause

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