Re: Postgres refusing to use >1 core

From: Shaun Thomas <sthomas(at)peak6(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Aren Cambre <aren(at)arencambre(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Postgres refusing to use >1 core
Date: 2011-05-11 16:04:49
Message-ID: 4DCAB3A1.2090507@peak6.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-05-11 16:38:01 Re: 'Interesting' prepared statement slowdown on large table join
Previous Message Shaun Thomas 2011-05-11 15:55:27 Re: 'Interesting' prepared statement slowdown on large table join