Hello,
Many thanks for the valuable suggestions and insights.
The defaults enable_bitmapscan and enable_seqscan were altered by my
friend. He already re enabled them (maybe even while I was trying some
of the queries).
The machine is a P4 3.2GHz, 1 GBram, sata hd, windows 2000. I did not
used pg on win before to have any advice to my friend.
The previously attached file contains SOME relevant info from the psql
session, in order to not clutter file.
When some server parameter was modified (at least by me) and server
restarted, a new sholl parameter was issued to show the new value.
Firebird is running at the same machine.
As you can see by the session log, indexes were created on the columns
used and tables was first clustered on the indexes actually used by the
query.
The subsequent cluster commands only recluster on the same indexes
previously clustered.
shared_buffers was increased from 1000 to 16384 pages
effective_cache_size was increased from 1000 to 65535 pages and at the
final steps REDUCED to 8192 pages
work_mem was increased from 1024 first to 16384 KB and then to 65535
KB.
The first 2 parameters reduced time 18%.
work_mem reduced time almost 66%.
But work_mem easily can exhaust ram with many users connected, as each
connection query will use this amount of memory (if I can remember).
How much it can grow at this 1 gbram win machine?
Some of the docs I already read suggested that indexes should be
entirely contained in ram. How to dimension the parameters?
Other docs adviced that some memory parameters could actually degrade
performance if too big. There are peak points at the performance curve
by adjusting mem parameters.
I hope tomorrow execute explain with the bitmapscan and seqscan enabled.
bitmapscans are almost always faster?
The data, as far I know, are a sample real app data (hey, if and when in
production it will be even large?). They are almost true random as my
friend informed, and according to him, cluster should not really be of
benefit. It seems confirmed by the various explain analyze commands
before and after clustering.
Any suggestions? Do you see some obvious error on the steps at the
previous session log file?
It seems that Firebird windows can use adequately as much ram it finds
and postgresql windows can not. How dimension ram to the indexes? Only
by trial and error? I tried some suggested values found at some tuning
docs suitable to the available system ram.
Thanks
Andre Felipe
