From: gnuoytr@rcn.com
To: pgsql-performance@postgresql.org
Subject: Re: Postgres refusing to use >1 core
Date: Wed, 11 May 2011 19:07:57 -0400 (EDT)
Message-ID: <201105112307.060223@ms14.lnh.mail.rcn.net> (raw)
In-Reply-To: <4DCB0802.9060505@peak6.com>
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>
<4DCB0802.9060505@peak6.com>
---- Original message ----
>Date: Wed, 11 May 2011 17:04:50 -0500
>From: pgsql-performance-owner@postgresql.org (on behalf of Shaun Thomas <sthomas@peak6.com>)
>Subject: Re: [PERFORM] Postgres refusing to use >1 core
>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>
>
>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
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnuoytr@rcn.com
Subject: Re: Postgres refusing to use >1 core
In-Reply-To: <201105112307.060223@ms14.lnh.mail.rcn.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox