Re: Performance issues migrating from 743 to 826

Hi Scott,
Thanks for your time
Regards
Matthew

Scott Marlowe wrote:
> On Jan 28, 2008 5:41 AM, Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk> 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
>