Re: Performance issues migrating from 743 to 826

[Matthew Lunnon <mlunnon@xxxxxxxxxxxxx>]

Hi Scott,
 Thanks for your time
Regards
Matthew

Scott Marlowe wrote:

On Jan 28, 2008 5:41 AM, Matthew Lunnon <mlunnon@xxxxxxxxxxxxx> wrote:
  

Hi

I am investigating migrating from postgres 743 to postgres 826 but
although the performance in postgres 826 seems to be generally better
there are some instances where it seems to be markedly worse, a factor
of up to 10.  The problem seems to occur when I join to more than 4
tables. Has anyone else experienced anything similar or got any
suggestions as to what I might do? I am running on an intel box with two
hyper threaded cores and 4GB of RAM. I have tweaked the postgres.conf
files with these values and the query and explain output are below. In
this case the query takes 6.037 ms to run on 862 and 2.332 to run on 743.
    

It looks like the data are not the same in these two environments.

  

8.2.6
shared_buffers = 500MB
work_mem = 10MB
maintenance_work_mem = 100MB
effective_cache_size = 2048MB
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.

Plan for 7.4:

  

"Nested Loop  (cost=37.27..48.34 rows=1 width=458) (actual
time=1.474..2.138 rows=14 loops=1)"
"  ->  Nested Loop  (cost=37.27..42.34 rows=1 width=282) (actual
time=1.428..1.640 rows=2 loops=1)"
    

This is processing 2 rows...

  

"Total runtime: 2.332 ms"
    

While this is processing 189 rows:

  

"Nested Loop  (cost=0.00..30.39 rows=1 width=458) (actual
time=0.123..5.841 rows=14 loops=1)"
"  ->  Nested Loop  (cost=0.00..29.70 rows=1 width=439) (actual
time=0.099..4.590 rows=189 loops=1)"
    

Hardly seems a fair comparison.
  

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.

_____________________________________________________________________
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