DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=EHrA0pNw7xsyZhCUONH7ptBNdGB204zavjfiCEFcl+pXmpYZGbutqA7u+y0BBXKvGR1L36m4it5FAPCvlMMY6OXzEyvauNusVNMSDoUwDdQgyPoZ88+geh+aANNJTnI0iNFNp/1Kc4GJkgOIEM6w67oPPnMrUCfakTS4JdDKdCU=
Message-ID: <dcc563d10801280714p1874cf80h27078179e2a42b18@mail.gmail.com>
Date: Mon, 28 Jan 2008 09:14:00 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: mlunnon@rwa-net.co.uk
Subject: Re: Performance issues migrating from 743 to 826
Cc: pgsql-performance@postgresql.org
In-Reply-To: <479DBF75.6080402@rwa-net.co.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <479DBF75.6080402@rwa-net.co.uk>

On Jan 28, 2008 5:41 AM, Matthew Lunnon <mlunnon@rwa-net.co.uk> wrote:
> Hi
>
> I am investigating migrating from postgres 743 to postgres 826 but
> although the performance in postgres 826 seems to be generally better
> there are some instances where it seems to be markedly worse, a factor
> of up to 10.  The problem seems to occur when I join to more than 4
> tables. Has anyone else experienced anything similar or got any
> suggestions as to what I might do? I am running on an intel box with two
> hyper threaded cores and 4GB of RAM. I have tweaked the postgres.conf
> files with these values and the query and explain output are below. In
> this case the query takes 6.037 ms to run on 862 and 2.332 to run on 743.

It looks like the data are not the same in these two environments.

> 8.2.6
> shared_buffers = 500MB
> work_mem = 10MB
> maintenance_work_mem = 100MB
> effective_cache_size = 2048MB
> default_statistics_target = 1000

That's very high for the default.  Planning times will be increased noticeably

Plan for 7.4:

> "Nested Loop  (cost=37.27..48.34 rows=1 width=458) (actual
> time=1.474..2.138 rows=14 loops=1)"
> "  ->  Nested Loop  (cost=37.27..42.34 rows=1 width=282) (actual
> time=1.428..1.640 rows=2 loops=1)"

This is processing 2 rows...

> "Total runtime: 2.332 ms"

While this is processing 189 rows:

> "Nested Loop  (cost=0.00..30.39 rows=1 width=458) (actual
> time=0.123..5.841 rows=14 loops=1)"
> "  ->  Nested Loop  (cost=0.00..29.70 rows=1 width=439) (actual
> time=0.099..4.590 rows=189 loops=1)"

Hardly seems a fair comparison.