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





---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andre,

Could not Postgresql file cache being killed by firebird activity?
Haven´t you tried decrease ramdom_page_cost to 3 or 2?

It would be better if only one person will make configuration changes,
otherwise it will be difficult to measure each configuration change impact.

Reimer



Andre Felipe Machado <andremachado@techforce.com.br> escreveu:
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





---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq



---------------------------------
Yahoo! Acesso Grátis
Internet rápida e grátis. Instale o discador agora!

> I hope tomorrow execute explain with the bitmapscan and seqscan enabled.
> bitmapscans are almost always faster?

Like all the rest, they're just a tool, which works great when used in
its intended purpose :

- Fetching just a few percent of the rows from a table is better served
by an index scan
- Fetching a lot of rows (>30-50%) from a table is better served by a seq
scan
- Bitmap scan comes in between and it's a very welcome addition.

Also Bitmap scan will save your life if you have complex searches, like
if you run a dating site and have an index on blondes and an index on boob
size, because it can use several indexes in complex AND/OR queries.

Common wisdom says simpler databases can be faster than postgres on
simple queries.

Reality check with pg 8.1 driven by PHP :

- SELECT 1
mysql 5 ~ 42 us
postgres ~ 70 us

- SELECT * FROM users WHERE id=1
mysql 5 ~ 180 us
postgres ~ 160 us

Of course people doing stupid things, like using the database to keep a
hit counter on their website which is updated on every hit, will say that
postgres is slow.

---------------------------(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

Andre Felipe Machado wrote:

>It seems that Firebird windows can use adequately as much ram it finds
>and postgresql windows can not.
>
>
PostgreSQL relies on the OS cache to utilize RAM. Make sure that most of
the RAM is 'available' so Windows can do its thing.

effective_cache_size should be set correspondingly high - at least 65535.

shared_buffers should be as low as you can get away with (allowing for
multiple users). 16384 is 12.5% of your RAM and far too high.

AFAIK, PostgreSQL still doesn't differentiate between index blocks and
data blocks.

>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?
>
>

work_mem has to be just big enough to allow hash joins to operate
efficiently. This varies from query to query and can be set in your code
accordingly. However, the 1024 default is just too low for most
applications and you'll probably find even 4096 is a huge improvement.
You need to find the minimum that delivers acceptable performance in
most queries and boost it for selected queries as required.

BTW, which version of Firebird is this?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend