On Thu, Jan 29, 2009 at 10:58 AM, <anders.blaagaard@xxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
