X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9AA3711F6275 for ; Wed, 26 Apr 2006 19:52:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 54846-04-3 for ; Wed, 26 Apr 2006 19:52:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 9ED0211F6AFB for ; Wed, 26 Apr 2006 19:52:02 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 511D856423; Wed, 26 Apr 2006 17:52:02 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 26 Apr 2006 17:52:01 -0500 Date: Wed, 26 Apr 2006 17:52:01 -0500 From: "Jim C. Nasby" To: Benjamin Krajmalnik Cc: Chris Hoover , pgsql-admin@postgresql.org Subject: Re: Table partitioning Message-ID: <20060426225201.GH97354@pervasive.com> References: <8511B4970E0D124898E973DF496F9B432515CC@stash.stackdump.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8511B4970E0D124898E973DF496F9B432515CC@stash.stackdump.local> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060426:kraj@illumen.com::Rqxxe/3axA2Ii1pP:01KTW X-Hashcash: 1:20:060426:revoohc@gmail.com::M+N1HGrqVZvo1LCE:00000000000000000000 0000000000000000000000002G0k X-Hashcash: 1:20:060426:pgsql-admin@postgresql.org::VUNQNlpMU7evh8cB:00000000000 0000000000000000000000000Gdb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/291 X-Sequence-Number: 21460 Can we see the output of \d tablename as well as EXPLAIN ANALYZE of the select? On Wed, Apr 26, 2006 at 02:48:50PM -0600, Benjamin Krajmalnik wrote: > Actually, right now there is no data in those partitions. > > All of the data is currently in the parent table (I have not yet created > the trigger which will route the data to the correct partition). > > I just found to items intriguing - first, that the indices and other > properties other than the field definition were not inherited (is this > how this is supposed to work?), and second, that PG first retrieves the > entire result set and then limits it (or at least that appear to be how > it is working). > > If the order by clause were an expression, I can understand where it > would have to first retrieve the entire resultset and then limit it. > However, when we are dealing with an order by clause running on an index > or primary key, I would figure that it would only retrieve the number of > rows limited, or if an offset is specified then go to the offset and > only process the "limit" number of rows. > > > > > > ________________________________ > > From: Chris Hoover [mailto:revoohc@gmail.com] > Sent: Wednesday, April 26, 2006 2:33 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Tale partitioning > > > > Each of the partition tables needs it's own set of indexes. Build them, > and see if the does not fix your performance issues. Also, be sure you > turned on the constraint_exclusion parameter, and each table (other than > the "master") has an constraint on it that is unique. > > HTH, > > Chris > > > > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461