From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
To: Scott Marlowe <scott.marlowe@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance issues migrating from 743 to 826
Date: Mon, 28 Jan 2008 15:54:07 +0000
Message-ID: <479DFA9F.5000009@rwa-net.co.uk> (raw)
In-Reply-To: <dcc563d10801280739h7b21720eqd54658e71cd5f115@mail.gmail.com>
References: <479DBF75.6080402@rwa-net.co.uk>
<dcc563d10801280714p1874cf80h27078179e2a42b18@mail.gmail.com>
<479DF476.4030800@rwa-net.co.uk>
<dcc563d10801280739h7b21720eqd54658e71cd5f115@mail.gmail.com>
Scott Marlowe wrote:
> 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. :)
>
Thanks again for your time. I'm using Thunderbird, maybe I need to upgrade.
> 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.
>
Thanks I haven't looked into this yet, I'll look. When I changed the
default_stats_target it did take a very long time to do its analyze so I
assumed it was doing something.
>
>> 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?
>
Thanks, I'll take a look at that, is there any documentation on what
8.2.6. can't use in an index? It didn't seem to have complained about
any of my indexes when I generated the database.
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> _____________________________________________________________________
> This e-mail has been scanned for viruses by Verizon Business Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.verizonbusiness.com/uk
>
--
Matthew Lunnon
Technical Consultant
RWA Ltd.
mlunnon@rwa-net.co.uk
Tel: +44 (0)29 2081 5056
www.rwa-net.co.uk
--
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: mlunnon@rwa-net.co.uk, scott.marlowe@gmail.com
Subject: Re: Performance issues migrating from 743 to 826
In-Reply-To: <479DFA9F.5000009@rwa-net.co.uk>
* 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