Re: Postgres refusing to use >1 core - Mailing list pgsql-performance
From | Shaun Thomas |
---|---|
Subject | Re: Postgres refusing to use >1 core |
Date | |
Msg-id | 4DCB0802.9060505@peak6.com Whole thread Raw |
In response to | Re: Postgres refusing to use >1 core (<gnuoytr@rcn.com>) |
Responses |
Re: Postgres refusing to use >1
core
Re: Postgres refusing to use >1 core Re: Postgres refusing to use >1 core |
List | pgsql-performance |
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
pgsql-performance by date: