Re: PostgreSQL 8.4 performance tuning questions

> OK, this seems to be the cause. What were the original values of the
> config variables? If you've lowered the work_mem and you need to sort a
> lot of data, this may be a problem. What amounts of data are you working
> with? If the data were not analyzed recently, the execution plans will be
> inefficient and this may be the result.

The reason is that i'm using config that i used before and it worked
perfect. work_mem is 192mb.
I tried ANALYZE, but it didn't change anything.

Amounts of data... at least, backup is over ~2.2Gb.
I tried to use EXPLAIN ANALYZE for slow queries that i get from
serverlog, but it also didn't change anything.

2009/7/30 <tv(at)fuzzy(dot)cz>:
>> Unfortunately had to downgrade back to 8.3. Now having troubles with
>> that and still solving them.
>>
>> For future upgrade, what is the basic steps?
>
> 1. create database
> 2. dump the data from the old database
> 3. load the data into the new database
> 4. analyze etc. (I prefer to do this manually at the beginning)
> 5. check that everything is working (that the correct execution plans are
> used, etc.)
>
> You may even run the (2) and (3) at once - use pipe instead of a file.
>
>>
>>>Was the database analyzed recently?
>> Hm... there was smth like auto analyzer in serverlog when i started it
>> first time, but i didn't mention that.
>> Should I analyze whole db? How to do it?
>
> Just execute 'ANALYZE' and the whole database will be analyzed, but when
> the autovacuum daemon is running this should be performed automatically (I
> guess - check the pg_stat_user_tables, there's information about last
> manual/automatic vacuuming and/or analysis).
>
>> And how should I change _cost variables?
>
> I haven't noticed you've not modified those variables, so don't change them.
>
>> I/O was very high. at first memory usage grew up and then began to full
>> swap.
>
> OK, this seems to be the cause. What were the original values of the
> config variables? If you've lowered the work_mem and you need to sort a
> lot of data, this may be a problem. What amounts of data are you working
> with? If the data were not analyzed recently, the execution plans will be
> inefficient and this may be the result.
>
> regards
> Tomas
>
>