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 5949C53522 for ; Fri, 25 Mar 2005 05:32:32 +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 50567-02 for ; Fri, 25 Mar 2005 05:32:23 +0000 (GMT) Received: from mailgate3.nau.edu (mailgate3.nau.edu [134.114.96.145]) by svr1.postgresql.org (Postfix) with ESMTP id 97F9753475 for ; Fri, 25 Mar 2005 05:32:22 +0000 (GMT) Received: from CONVERSION-DAEMON.mailgate3.nau.edu by mailgate3.nau.edu (PMDF V6.2-X17 #30845) id <0IDW00I017DXN7@mailgate3.nau.edu> for pgsql-performance@postgresql.org; Thu, 24 Mar 2005 22:32:22 -0700 (MST) Received: from k2.cet.nau.edu (net9-213.cet.nau.edu [134.114.9.213]) by mailgate3.nau.edu (PMDF V6.2-X17 #30845) with ESMTP id <0IDW0046R7DXGA@mailgate3.nau.edu>; Thu, 24 Mar 2005 22:32:21 -0700 (MST) Date: Thu, 24 Mar 2005 22:35:55 -0700 From: Karim Nassar Subject: Re: Delete query takes exorbitant amount of time In-reply-to: <200503242124.39558.josh@agliodbs.com> To: Josh Berkus Cc: pgsql-performance@postgresql.org Message-id: <1111728955.9085.238.camel@k2.cet.nau.edu> MIME-version: 1.0 X-Mailer: Evolution 2.0.2 Content-type: text/plain Content-transfer-encoding: 7BIT References: <1111709457.9085.127.camel@k2.cet.nau.edu> <2630.1111711927@sss.pgh.pa.us> <1111715304.9089.179.camel@k2.cet.nau.edu> <200503242124.39558.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/595 X-Sequence-Number: 11300 On Thu, 2005-03-24 at 21:24 -0800, Josh Berkus wrote: > Karim, > How about getting some decent disk support? A single 10K SCSI disk is a bit > sub-par for a database with 100's of millions of records. Too bad you didn't > get a v40z ... Hehe. I have one I am setting up that will be dedicated to postgresql, hence my question about a week ago about disk partitioning/striping :-) > Beyond that, you'll want to do the same thing whenever you purge the > referencing table; drop keys, delete, re-create keys. Or think about why it > is you need to delete batches of records from this FKed table at all. The database is for weather data from multiple sources. When adding a new dataset, I have to create/test/delete/recreate the config in the FKed table. Users don't have this power, but I need it. Drop/delete/recreate is a totally acceptable solution for this scenario. I guess I was wondering if there is other general tuning advice for such large table indexes such as increasing statistics, etc. Thanks, -- Karim Nassar Department of Computer Science Box 15600, College of Engineering and Natural Sciences Northern Arizona University, Flagstaff, Arizona 86011 Office: (928) 523-5868 -=- Mobile: (928) 699-9221