Re: Performance issues migrating from 743 to 826

[Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk>]

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