Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id CE5A81337B69 for ; Wed, 11 May 2011 13:04:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 30314-06 for ; Wed, 11 May 2011 16:04:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp2.peak6.com (smtp2.peak6.com [38.98.137.9]) by mail.postgresql.org (Postfix) with ESMTP id A69071337BE1 for ; Wed, 11 May 2011 13:04:50 -0300 (ADT) X-AuditID: 0a02cb1e-b7b7eae000002a28-da-4dcab3a148ad Message-ID: <4DCAB3A1.2090507@peak6.com> Date: Wed, 11 May 2011 11:04:49 -0500 From: Shaun Thomas Reply-To: Organization: Peak6 User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1 MIME-Version: 1.0 To: Scott Marlowe CC: Craig Ringer , Aren Cambre , Subject: Re: Postgres refusing to use >1 core References: <4DC81A3F020000250003D433@gw.wicourts.gov> <4DC81D56020000250003D438@gw.wicourts.gov> <4DC8B893.3060908@postnewspapers.com.au> <4DC9E7C5.9080309@postnewspapers.com.au> In-Reply-To: Content-Type: text/plain; charset="ISO-8859-1"; format=flowed content-transfer-encoding: 7bit X-Originating-IP: [10.10.221.50] X-Brightmail-Tracker: AAAAAQAAAZE= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.595 tagged_above=-10 required=5 tests=BAYES_00=-1.9, SARE_MILLIONSOF=0.315, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201105/152 X-Sequence-Number: 43661 On 05/10/2011 11:26 PM, Scott Marlowe wrote: > I.e. don't grab 1,000 rows and work on them on the client side and > then insert data, do the data mangling in the query in the database. > My experience has been that moving things like this into the database > can result in performance gains of several factors, taking hour long > processes and making them run in minutes. This is a problem I encounter constantly wherever I go. Programmer selects millions of rows from giant table. Programmer loops through results one by one doing some magic on them. Programmer submits queries back to the database. Even in batches, that's going to take ages. Databases are beasts at set-based operations. If the programmer can build a temp table of any kind and load that, they can turn their update/insert/whatever into a simple JOIN that runs several orders of magnitude faster. Going the route of parallelism will probably work too, but I doubt it's the right solution in this case. When there are tables with millions of rows involved, processing 111 per second is a bug. Even with ten perfectly balanced threads, 30 hours only becomes three. On decent hardware, you can probably drop, reload, and index the entire table faster than that. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@peak6.com ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email