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
