Re: Performance issues migrating from 743 to 826

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(at)rwa-net(dot)co(dot)uk> wrote:
>
>> Scott Marlowe wrote:
>> On Jan 28, 2008 5:41 AM, Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)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(at)rwa-net(dot)co(dot)uk
Tel: +44 (0)29 2081 5056
www.rwa-net.co.uk
--