---- Original message ---- >Date: Wed, 11 May 2011 17:04:50 -0500 >From: pgsql-performance-owner@xxxxxxxxxxxxxx (on behalf of Shaun Thomas <sthomas@xxxxxxxxx>) >Subject: Re: Postgres refusing to use >1 core >To: <gnuoytr@xxxxxxx> >Cc: Scott Marlowe <scott.marlowe@xxxxxxxxx>,Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx>,Aren Cambre <aren@xxxxxxxxxxxxxx>,<pgsql-performance@xxxxxxxxxxxxxx> > >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 You're (both) fortunate to have Suits and colleagues who are open to doing this A Better Way. Bless you. Regards, Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
