X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C88E13A42C0 for ; Wed, 17 Nov 2004 05:31:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30312-07 for ; Wed, 17 Nov 2004 05:31:22 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.202]) by svr1.postgresql.org (Postfix) with ESMTP id DAA493A4227 for ; Wed, 17 Nov 2004 05:31:16 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 71so371710wra for ; Tue, 16 Nov 2004 21:31:15 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=fxi5vrrv/Vcf8UgkUACDifEUOltVaaH7q/s5uW6LppMNKmsccvVpi9LF7JjcCd5E13206UmnPXu2Ftc+noH9epKI3paa1JfynGAjY0WAlxpT6D8w6E79QuxWOlc8rUvNOtz4RPO0/4rz2tjt+IcF5+vF6X4h2ghsSqzBoZW3Pko= Received: by 10.54.18.78 with SMTP id 78mr644693wrr; Tue, 16 Nov 2004 21:31:15 -0800 (PST) Received: by 10.54.52.30 with HTTP; Tue, 16 Nov 2004 21:31:15 -0800 (PST) Message-ID: <6c21003b041116213166af022d@mail.gmail.com> Date: Tue, 16 Nov 2004 23:31:15 -0600 From: Don Drake Reply-To: Don Drake To: pgsql-performance@postgresql.org Subject: Table Partitions: To Inherit Or Not To Inherit Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/238 X-Sequence-Number: 9192 I've read the previous thread on the list regarding partitioning mechanisms and I just wrote a plpgsql function to create the partition tables (by date) as well as another function used to do the insert (it determines which table will be inserted). The creation of the partition tables uses the inherits clause when creating. It creates an exact copy of the table it's inheriting from, and adds the indexes since inherits doesn't do that for me. CREATE TABLE hourly_report_data_2004_11_16 () INHERITS (hourly_report_data) When I query on the hourly_report_data, the explain plan shows it query all the tables that inherited from it. That's all great. What's really the difference between this and creating separate tables with the same column definition without the inherit, and then create a view to "merge" them together? Also, I've run into a snag in that I have a hourly_detail table, that has a foreign key to the hourly_report_data. The inherit method above does not honor the foreign key relationship to the children table of hourly_report_data. I can't insert any data into the hourly_detail table due to the constraint failing. The hourly_detail table is relatively tiny compared to the enormous hourly_report_data table, so if I don't have to partition that one I would rather not. Any suggestions on this? Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574