Message-ID: <4DCB0802.9060505@peak6.com>
Date: Wed, 11 May 2011 17:04:50 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
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: <gnuoytr@rcn.com>
CC: Scott Marlowe <scott.marlowe@gmail.com>, Craig Ringer
	<craig@postnewspapers.com.au>, Aren Cambre <aren@arencambre.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Postgres refusing to use >1 core
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>
In-Reply-To: <201105111953.060147@ms14.lnh.mail.rcn.net>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

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