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=fDnqXRS09VpkaCgpOAc3eDNg2xeTQmQ7+wOZFhNvToqoGtgtbrfqYLUk8VstxKJkgVrVtThwYFdIOtFM+vYhbysuZ3QR6DIlV50O21nK+UR2G1DG8rilE0pQpVfWUOb1xAc7/cPaJkGv2ShZbRJb8D68y62nkmJI/FtcwmEOmNk=
Message-ID: <dcc563d10801280739h7b21720eqd54658e71cd5f115@mail.gmail.com>
Date: Mon, 28 Jan 2008 09:39:54 -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: <479DF476.4030800@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>
	<dcc563d10801280714p1874cf80h27078179e2a42b18@mail.gmail.com>
	<479DF476.4030800@rwa-net.co.uk>

Whatever email agent you're using seems to be quoting in a way that
doesn't get along well with gmail, so I'm just gonna chop most of it
rather than have it quoted confusingly...  Heck, I woulda chopped a
lot anyway to keep it small. :)

On Jan 28, 2008 9:27 AM, Matthew Lunnon <mlunnon@rwa-net.co.uk> wrote:
>
>  Scott Marlowe wrote:
>  On Jan 28, 2008 5:41 AM, Matthew Lunnon <mlunnon@rwa-net.co.uk> wrote:
> default_statistics_target = 1000
> >  That's very high for the default. Planning times will be increased
> > noticeably
>
>  I had originally left the default_statistics_target at its default and then
> increased it to 100, but this did not seem to make much difference.  I will
> reduce this down to something more normal again.

You do know that if you create a column when the default is 10, then
increase the default, it won't change the column's stats target,
right?  So, assuming the table was first created, then you changed the
default, you'll now need to do:

alter table xyz alter column abc set statistics 100;
analyze xyz;

for it to make any difference.

>  The queries were on exactly the same data. My interpretation of what is
> going on here is that 8.2.6 seems to be leaving the filtering of market_id
> to the very last point, which is why it ends up with 189 rows at this point
> instead of the 2 that 743 has. 743 seems to do that filtering much earlier
> and so reduce the number of rows at a much earlier point in the execution of
> the query. I guess that this is something to do with the planner which is
> why I tried increasing the default_statistics_target.

Ahh, I'm guessing it's something that your 7.4 database CAN use an
index on and your 8.2 data base can't use an index on.  Like text in a
non-C locale.  Or something...  Table def?