On 05/11/2011 02:53 PM, gnuoytr@xxxxxxx 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@xxxxxxxxx
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
