Hi,
If I have a view like:
create view X as (
select x from A
union all
select x from B)
and do
select max(x) from X
I get a plan like:
Aggregate
Append
Seq Scan on A
Seq Scan on B
If A and B are indexed on x, I can get the result much faster as:
select max(x) from (
select max(x) from A
union all
select max(x) from B) X
with the plan:
Aggregate
Append
Result
Limit
Index Scan Backward using .. on A
Result
Limit
Index Scan Backward using .. on B
My question is basically why the optimizer doesn't do this? Is it hard, or is it just something that hasn't been done yet?
My guess is that the second plan would always be as fast or faster than the first one - even if A and B wasn't indexed?
Anders