Re: Postgres views cannot use both union and join/where

On Tue, Oct 19, 2021 at 2:48 PM Mithran Kulasekaran <
mithranakulasekaran(at)gmail(dot)com> wrote:

> i think the only problem is when we try to use both union and where/join
> the issue starts to happen
>

I'm unconvinced this is actually an issue based upon what is presented
here. All I'm seeing is two decidedly different queries resulting in
different query plans. That the "problem one" isn't using an index isn't
surprising given the volume of data involved and the change from specifying
a literal value in the where clause to letting a join determine which
results to return.

Assuming you have a real scenario you are testing with being able to
demonstrate (probably through the use of the query planner GUCs) that
PostgreSQL can produce a better plan but doesn't by default would be a more
compelling case. More generally, you probably need to either use your real
scenario's data to help demonstrate the issue or create a self-contained
test that is at least closer to what it produces (this approach still
benefits from seeing what is happening for real).

David J.