Skip site navigation (1) Skip section navigation (2)

Postgresql 8.1.4 - performance issues for select on view using max

From: Ioana Danes <ioanasoftware(at)yahoo(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Postgresql 8.1.4 - performance issues for select on view using max
Date: 2006-10-18 19:51:34
Message-ID: (view raw or flat)
Lists: 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,

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

Here are the results of the explain analyze:

1. Select only from one table is OK:

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

 Result  (cost=0.04..0.05 rows=1 width=0)
     ->  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

QUERY PLAN                                            
 Aggregate  (cost=200579993.70..200579993.71 rows=1
width=8) (actual time=115778.101..115778.103 rows=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

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

Thank you for your time,

Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 


pgsql-performance by date

Next:From: Dimitri FontaineDate: 2006-10-18 20:13:17
Subject: Re: Postgresql 8.1.4 - performance issues for select on view using max
Previous:From: Merlin MoncureDate: 2006-10-18 19:37:07
Subject: Re: Optimization of this SQL sentence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group