Postgresql 8.1.4 - performance issues for select on view using max

Hi everyone,

I am doing a test for a scenario where I have 2
schemas one (public) for the operational data and
another one (archive) for old, archived data. So
basically I want to split the data from some huge
tables in two. All data before 2006 in archive and all
data after and including 2006 in public.

Let's say I have a table named public.AllTransactions
with data before and including 2006.
I want to move all the data < 2006 into a new table
named archive.transaction (in archive schema)
I also want to move all data >= 2006 into a new table
named public.transaction (in public schema).

In order to make this transparent for the developers I
want to drop the original table public.AllTransactions
and to create a view with the same name that is a
union between the two new tables:

create view public.AllTransactions as
select * from public.transaction
union all
select * from archive.transaction

On this view I will create rules for insert, update,
delete...

Testing some selects I know we have in the application
I got into a scenario where my plan does not work
without doing code change. This scenario is:

select max(transid) from alltransaction;

because the planner does not use the existent indexes
on the 2 new tables: public.transaction and
archive.transaction

Here are the results of the explain analyze:

1. Select only from one table is OK:
-------------------------------------

# explain select max(transid) from public.transaction;

QUERY
PLAN

--------------------------------------------------------------------------------

----------------------
Result (cost=0.04..0.05 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.04 rows=1 width=8)
-> Index Scan Backward using
pk_transaction on transaction (cost=0.00..357870.46

rows=9698002 width=8)
Filter: (transid IS NOT NULL)
(5 rows)

2. Select from the view is doing a sequential scan:
---------------------------------------------------
# explain analyze select max(transid) from
alltransaction;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
-----------------
Aggregate (cost=200579993.70..200579993.71 rows=1
width=8) (actual time=115778.101..115778.103 rows=1
loops=1)
-> Append (cost=100000000.00..200447315.74
rows=10614237 width=143) (actual time=0.082..95146.144
rows=10622206 loops= 1)
-> Seq Scan transaction
(cost=100000000.00..100312397.02 rows=9698002
width=143) (actual time=0.078..56002.778 rows=
9706475 loops=1)
-> Seq Scan on transaction
(cost=100000000.00..100028776.35 rows=916235
width=143) (actual time=8.822..2799.496 rows=
915731 loops=1)
Total runtime: 115778.200 ms
(5 rows)

Is this a bug or this is how the planner is suppose to
work?

The same problem I have on the following select:
select transid from alltransaction order by transid
desc limit 1;

Thank you for your time,
Ioana

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com