From: Ioana Danes <ioanasoftware@yahoo.ca>
To: pgsql-performance@postgresql.org
Subject: Postgresql 8.1.4 - performance issues for select on view using max
Date: Wed, 18 Oct 2006 15:51:34 -0400 (EDT)
Message-ID: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: ioanasoftware@yahoo.ca
Subject: Re: Postgresql 8.1.4 - performance issues for select on view using max
In-Reply-To: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com>
References: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox