Re: Max on union

On Thu, Jan 29, 2009 at 10:58 AM, <anders(dot)blaagaard(at)nordea(dot)com> wrote:
> 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?

Well, it's certainly not going to be faster without the index. You
can't very well do an index scan backward without an index.

As for why it doesn't do that, I don't think a huge amount of effort
has been put into optimizing the handling of appendrels. Patches are
welcome....

...Robert