I thought a union mashed together two queries. The where clause can appear in both. But the execution plan will almost certainly run the first query and the second query. It should throw an error if the types don't match or the number of columns don't match.
There are so few use cases for unions that can't get fixed with better schema designs. I ran into a few over the years.
On Tue, Oct 19, 2021, 9:32 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Tue, Oct 19, 2021 at 2:48 PM Mithran Kulasekaran <mithranakulasekaran@xxxxxxxxx> wrote:i think the only problem is when we try to use both union and where/join the issue starts to happenI'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.
