DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=r8xpNoKAW2RfAe1EhIbOHS3tE2kYzK8bAROvQXJgDLgmnpdl6B/edSBQ3YtQlo9iOR
	YATW8167diD/7gGXfVK6QJKYip8Sa7+z3OHZkxzExfAlkttLGVQZGBVGWFcGwg/Qrvs3
	2uGSGDvpzHKiisQCyf1wv1LGUay2tBY39mGdY=
MIME-Version: 1.0
In-Reply-To: <1CA7FF980DA3824F9A5C31532B7A40DCC4B87E10@LCXCLMB01.LCDS.LOC.GOV>
References: <1CA7FF980DA3824F9A5C31532B7A40DCC4B87E10@LCXCLMB01.LCDS.LOC.GOV>
Date: Mon, 22 Mar 2010 16:32:22 -0600
Message-ID: <dcc563d11003221532j51c88123tbd71fc0144a33775@mail.gmail.com>
Subject: Re: Disparity between 8.1.18 and 8.2.14 performance wise
From: Scott Marlowe <scott.marlowe@gmail.com>
To: "Dai, Tino" <tdai@loc.gov>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 22, 2010 at 3:58 PM, Dai, Tino <tdai@loc.gov> wrote:
> After many days of googling and referring to different web pages about pe=
rformance, I'm
> turning to this list for help. We have a third party application that is =
running on 8.1.11 and the
> vendor has told us not to upgrade the database to 8.2.
>
> I have gone with the default values in postgresql.conf of 8.1.18 (the 8.1=
.11 was unavailable
> from the postgresql site so this is the closest one) and 8.2.14. I execut=
e the same query with
> EXPLAIN ANALYZE on 8.1.18 and the 8.2.14. The 8.1.18 comes back with abou=
t 1000ms -1100ms
> while the 8.2.14 comes back with 5ms - 6ms.

That's a factor of about 200 times faster for 8.2.

> I have tried up the work_mem, effective_cache_size, and the shared_buffer=
s with no noticeable effect.
> I'm not looking for a magic wand to allow 8.1.18 to become 8.2.14, but so=
me performance gain would be
> nice. Can any shed some light on this?

Why?

> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (((section)::text =3D (section)::text=
) AND (ref_id =3D ref_id))
> =A0Total runtime: 6.905 ms

Is this the result from 8.2?  Cause it's not 6 seconds, it's 6 milliseconds=
..