Thread: Max on union

On Thu, Jan 29, 2009 at 10:58 AM,  <anders.blaagaard@nordea.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