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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Fri, Mar 10, 2006 at 10:39:57PM -0300, Andre Felipe Machado wrote:
> It seems that effective_cache_size does not tell postgresql to actually
> use windows disk cache.

No, it just tells PostgreSQL how much cache memory it should expect to
have.

> What parameter must be configured?
> Do you have some suggestions?

Well, you could try increasing shared_buffers, but the real question is
why Windows isn't caching the data. Are you sure that the data you're
reading is small enough to fit entirely in memory? Remember that
Firebird has a completely different on-disk storage layout than
PostgreSQL, so just because the table fits in memory there doesn't mean
it will do so on PostgreSQL.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly