Max on union - Mailing list pgsql-performance

From
Subject Max on union
Date
Msg-id BAEC438B92BD7C44B27A376937D9E41DE070C2@CCD1XM1003.ccd1.root4.net
Whole thread Raw
Responses Re: Max on union  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-performance
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
 

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: NOT IN >2hrs vs EXCEPT < 2 sec.
Next
From: "Hari, Balaji"
Date:
Subject: Re: LIKE Query performance