Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id BDBE89FA5D3 for ; Wed, 18 Oct 2006 16:58:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 69104-01 for ; Wed, 18 Oct 2006 19:58:16 +0000 (UTC) X-Greylist: delayed 00:06:40.085258 by SQLgrey- Received: from web55902.mail.re3.yahoo.com (web55902.mail.re3.yahoo.com [216.252.110.63]) by postgresql.org (Postfix) with SMTP id 05DB09FA4EE for ; Wed, 18 Oct 2006 16:58:15 -0300 (ADT) Received: (qmail 72987 invoked by uid 60001); 18 Oct 2006 19:51:34 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.ca; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=Wd5mBwGi+7hKEYtOTpdxI/EqfRt6cyWTw37uDCz8tbwZDUEf1gowZ6vVidIVVQ6WAL/4PUTVC+Xwq3N1ShP485w8XwZq3FR1mPqD5p+9WNQ9l86BFsCgTr8fEFR+RBGMEDofagH7HJvlt4ZvV5h7iqK/gzJp8jyBIvxb1+wjNso= ; Message-ID: <20061018195134.72985.qmail@web55902.mail.re3.yahoo.com> Received: from [207.164.182.8] by web55902.mail.re3.yahoo.com via HTTP; Wed, 18 Oct 2006 15:51:34 EDT Date: Wed, 18 Oct 2006 15:51:34 -0400 (EDT) From: Ioana Danes Subject: Postgresql 8.1.4 - performance issues for select on view using max To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.2 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200610/369 X-Sequence-Number: 21498 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