From: Shaun Thomas <sthomas@peak6.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: Aren Cambre <aren@arencambre.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres refusing to use >1 core
Date: Wed, 11 May 2011 11:04:49 -0500
Message-ID: <4DCAB3A1.2090507@peak6.com> (raw)
In-Reply-To: <BANLkTi=PxROPQFDt1a6N68e_+5_AojagmA@mail.gmail.com>
References: <BANLkTimohiUa6rOYdxYTwqmvSUg2CRYMHg@mail.gmail.com>
<4DC81A3F020000250003D433@gw.wicourts.gov>
<BANLkTimNTLqQgQeyu=FPOn-5x6F4XnVBJA@mail.gmail.com>
<4DC81D56020000250003D438@gw.wicourts.gov>
<BANLkTi=FH4rX-7OtS9ozjuyXQTNTiRqCmg@mail.gmail.com>
<BANLkTinwkj7syvKkYzU_O7DD14EMzk2Zmg@mail.gmail.com>
<BANLkTi=BvGMvsBNq8DUt=_MPJXZjcC0TWg@mail.gmail.com>
<4DC8B893.3060908@postnewspapers.com.au>
<BANLkTimi+2nviLvWU4zKGtG688+Z84DF6w@mail.gmail.com>
<4DC9E7C5.9080309@postnewspapers.com.au>
<BANLkTi=PxROPQFDt1a6N68e_+5_AojagmA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, scott.marlowe@gmail.com, craig@postnewspapers.com.au, aren@arencambre.com
Subject: Re: Postgres refusing to use >1 core
In-Reply-To: <4DCAB3A1.2090507@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox