Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 1A018634DC0
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  6 Mar 2009 11:50:33 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 71065-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri,  6 Mar 2009 11:50:26 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.28])
	by mail.postgresql.org (Postfix) with ESMTP id 43DF563440B
	for <pgsql-performance@postgresql.org>;
	Fri,  6 Mar 2009 11:50:24 -0400 (AST)
Received: by yw-out-2324.google.com with SMTP id 5so244338ywh.73
	for <pgsql-performance@postgresql.org>;
	Fri, 06 Mar 2009 07:50:22 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:content-type;
	bh=/RtPBx18s8PzLnEhvcqAt/wKKaRcFlzJZZ9TsHqeSmo=;
	b=qLiUaGfi69ahXaWRbZM+82BrgbkeHQB3/NGDDPZ1V3NDPCcMH3qQpzt5QKfnjmh7xJ
	Q8J8dh7osiEKn+hYVogVgHmxZQv/TXlVwTFEtapsRF0aBl9MISw4y/UC9b4LTAnN6dgP
	zADq3mN0nERqIXXwFrp6qOyvgEZydqg3TscB4=
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
	:content-type;
	b=fQBEAK6g0oK+SY2svK3oVBFD8M4rUwTqUSNjGWnPtNRvwCu9zUzK5pD9h0ha32M9hy
	MWmzuto4mDqX8jzJdUt9yQc9Hw7sFAdhxUCrzvq4lKM1Vq13Xylb1Bz/A4fiHQL00fSP
	TGREIGC7TvoG0FMO5KJ3Oa4v0YPdh3d9vAp4Y=
MIME-Version: 1.0
Received: by 10.101.67.11 with SMTP id u11mr924031ank.116.1236354622351; Fri,
	06 Mar 2009 07:50:22 -0800 (PST)
In-Reply-To: <1236275907.6979.40.camel@jd-laptop.pragmaticzealot.org>
References: <f0afbb9b0903021618w616f1158t84dbf119f0b72c70@mail.gmail.com>
	<2870.1236050632@sss.pgh.pa.us>
	<f0afbb9b0903030931g7b219137n2954cf058d523e52@mail.gmail.com>
	<1236275907.6979.40.camel@jd-laptop.pragmaticzealot.org>
Date: Fri, 6 Mar 2009 10:50:22 -0500
Message-ID: <f0afbb9b0903060750v17fff813if1cbc6df10813c48@mail.gmail.com>
Subject: Re: Postgres 8.3, four times slower queries?
From: Aaron Guyon <battlemage@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=0016368e1d8a14e9460464753e75
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200903/59
X-Sequence-Number: 32981

--0016368e1d8a14e9460464753e75
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com>wrote:

> What happens if you do this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> EXPLAIN ANALYZE 8.3 query....
>
> Next try this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> SET seq_page_cost TO '4.0';
> SET random_page_cost TO '1.0';
> EXPLAIN ANALYZE 8.3 query....
>
> And then this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> SET seq_page_cost TO '4.0';
> SET random_page_cost TO '1.0';
> SET effective_cache_size TO '3000MB';
> EXPLAIN ANALYZE 8.3 query....
>

These three are pretty much the same in terms of performance.  I stayed with
the first one (cpu_tuple_cost = 0.5 and cpu_index_tuple_cost = 0.5).  As
shown earlier, it gives a result similar or slightly better than 8.2.12 in
terms of performance and response time.  The explain analyze shows that the
query no longer causes postgreSQL to uses hashes, but indexes instead which
boosted the performance of the query from ~1200 ms to ~600 ms.

Thank you everyone for all the help and feedback on this issue.

--0016368e1d8a14e9460464753e75
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <span dir=3D"ltr">&lt;<a h=
ref=3D"mailto:jd@commandprompt.com">jd@commandprompt.com</a>&gt;</span> wro=
te:<br><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=
=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; p=
adding-left: 1ex;">
What happens if you do this:<br>
<br>
SET cpu_tuple_cost TO &#39;0.5&#39;;<br>
SET cpu_index_tuple_cost TO &#39;0.5&#39;;<br>
EXPLAIN ANALYZE 8.3 query....<br>
<br>
Next try this:<br>
<br>
SET cpu_tuple_cost TO &#39;0.5&#39;;<br>
SET cpu_index_tuple_cost TO &#39;0.5&#39;;<br>
SET seq_page_cost TO &#39;4.0&#39;;<br>
SET random_page_cost TO &#39;1.0&#39;;<br>
EXPLAIN ANALYZE 8.3 query....<br>
<br>
And then this:<br>
<br>
SET cpu_tuple_cost TO &#39;0.5&#39;;<br>
SET cpu_index_tuple_cost TO &#39;0.5&#39;;<br>
SET seq_page_cost TO &#39;4.0&#39;;<br>
SET random_page_cost TO &#39;1.0&#39;;<br>
SET effective_cache_size TO &#39;3000MB&#39;;<br>
EXPLAIN ANALYZE 8.3 query....<br>
</blockquote></div><br>These three are pretty much the same in terms of per=
formance.=A0 I stayed
with the first one (cpu_tuple_cost =3D 0.5 and cpu_index_tuple_cost =3D
0.5).=A0 As shown earlier, it gives a result similar or slightly better tha=
n 8.2.12 in
terms of performance and response time.=A0 The explain analyze shows that t=
he query no longer causes postgreSQL to uses hashes, but indexes instead wh=
ich boosted the performance of the query from ~1200 ms to ~600 ms.<br>
<br>Thank you everyone for all the help and feedback on this issue.<br>

--0016368e1d8a14e9460464753e75--