From: Shaun Thomas <sthomas@peak6.com>
To: gnuoytr@rcn.com
Cc: 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 17:04:50 -0500
Message-ID: <4DCB0802.9060505@peak6.com> (raw)
In-Reply-To: <201105111953.060147@ms14.lnh.mail.rcn.net>
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>
<4DCAB3A1.2090507@peak6.com>
<201105111953.060147@ms14.lnh.mail.rcn.net>
On 05/11/2011 02:53 PM, gnuoytr@rcn.com wrote:
> So, the $64 question: how did you find an engagement where, to bend
> Shakespeare, "first thing we do, is kill all the coders" isn't
> required?
It's just one of those things you have to explain. Not just how to fix
it, but *why* doing so fixes it. It's also not really a fair expectation
in a lot of ways. Even when a coder uses all SQL, their inexperience in
the engine can still ruin performance. We spend years getting to know
PostgreSQL, or just general DB techniques. They do the same with coding.
And unless they're a developer for a very graphics intensive project,
they're probably not well acquainted with set theory.
Just today, I took a query like this:
UPDATE customer c
SET c.login_counter = a.counter
FROM (SELECT session_id, count(*) as counter
FROM session
WHERE date_created >= CURRENT_DATE
GROUP BY session_id) a
WHERE c.process_date = CURRENT_DATE
AND c.customer_id = a.session_id
And suggested this instead:
CREATE TEMP TABLE tmp_login_counts AS
SELECT session_id, count(1) AS counter
FROM auth_token_arc
WHERE date_created >= CURRENT_DATE
GROUP BY session_id
UPDATE reporting.customer c
SET login_counter = a.counter
FROM tmp_login_counts a
WHERE c.process_date = CURRENT_DATE
AND c.customer_id = a.session_id
The original query, with our very large tables, ran for over *two hours*
thanks to a nested loop iterating over the subquery. My replacement ran
in roughly 30 seconds. If we were using a newer version of PG, we could
have used a CTE. But do you get what I mean? Temp tables are a fairly
common technique, but how would a coder know about CTEs? They're pretty
new, even to *us*.
We hold regular Lunch'n'Learns for our developers to teach them the
good/bad of what they're doing, and that helps significantly. Even hours
later, I see them using the techniques I showed them. The one I'm
presenting soon is entitled '10 Ways to Ruin Performance' and they're
all specific examples taken from day-to-day queries and jobs here, all
from different categories of mistake. It's just a part of being a good DBA.
--
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, gnuoytr@rcn.com, scott.marlowe@gmail.com, craig@postnewspapers.com.au, aren@arencambre.com
Subject: Re: Postgres refusing to use >1 core
In-Reply-To: <4DCB0802.9060505@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