Skip site navigation (1) Skip section navigation (2)

Re: Performance issues migrating from 743 to 826

From: Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance issues migrating from 743 to 826
Date: 2008-01-28 15:54:07
Message-ID: 479DFA9F.5000009@rwa-net.co.uk (view raw or flat)
Thread:
Lists: pgsql-performance

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
--

In response to

pgsql-performance by date

Next:From: Claire McListerDate: 2008-01-28 17:59:07
Subject: JDBC/Stored procedure performance issue
Previous:From: Matthew LunnonDate: 2008-01-28 15:49:25
Subject: Re: Performance issues migrating from 743 to 826

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group