Re: Postgres refusing to use >1 core

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