Postgresql 8.1.4 - performance issues for select on view using max - Mailing list pgsql-performance

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