Hello,
I got good results on tuning postgresql performance for my friend.
One of the queries took almost 10 minutes.
Now it completes on 26 miliseconds! (at the second run)
A combination of query otimization, indexes choosing (with some droping
and clustering), server parameters reconfigurations.
Firebird still execute it on almost 2 minutes, much slower.
Firebird is much slower than Postgresql at queries without joins.
Postgresql is lightning faster than Firebird when manually tunned and
without using joins and aggregates functions.
The example query and its explain analyze results are attached, with the
"show all" output of each config iteration, and indexes created.
(UPDATE: i am sending msg from home and does not have the correct log
file here. Will send the file at monday....)
BUT
there are some issues still unknown.
The example query executes consistently at 56 seconds, and even at 39
seconds.
Firebird executes the same query at 54 seconds the first time and at 20
seconds at next times.
Today I went to the machine (was previously executing pg commands
remotely) to observe the windows behaviour.
Postgresql uses around 30% cpu and hard disk heavily (not so as vacuum)
at all executions.
Firebird uses around 40% cpu and hard disk heavily at the first
execution.
The second execution uses around 60% cpu and **NO** disk activity.
The previously cited query running at 26 miliseconds down from 10
minutes, can achieve this performance at the second run, with **NO**
disk activity.
At the first run it uses 1,7 seconds, down from 10 minutes.
The hard disk is clearly a bottleneck.
1,7 seconds against 26 miliseconds.
So,
How "convince" postgresql to use windows disk cache or to read all
indexes to ram?
It seems that effective_cache_size does not tell postgresql to actually
use windows disk cache.
What parameter must be configured?
Do you have some suggestions?
Regards.
Andre Felipe Machado
www.techforce.com.br
