On 2011-01-12 15:36, Kevin Grittner wrote:
Laszlo Nagy<gandalf@xxxxxxxxxxxx> wrote:
shared_mem = 6GB
work_mem = 512MB
total system memory=24GB
In addition to the good advice from Ken, I suggest that you set
effective_cache_size (if you haven't already). Add whatever the OS
shows as RAM used for cache to the shared_mem setting.
It was 1GB. Now I changed to 2GB. Although the OS shows 9GB inactive
memory, we have many concurrent connections to the database server. I
hope it is okay to use 2GB.
But yeah, for your immediate problem, if you can cluster the table
on the index involved, it will be much faster. Of course, if the
table is already in a useful order for some other query, that might
get slower, and unlike some other products, CLUSTER in PostgreSQL
doesn't *maintain* that order for the data as new rows are added --
so this should probably become a weekly (or monthly or some such)
maintenance operation.
Thank you! After clustering, queries are really fast. I don't worry
about other queries. This is the only way we use this table - get
details for a given id value. I put the CLUSTER command into a cron
script that runs daily. For the second time, it took 2 minutes to run so
I guess it will be fine.
Thank you for your help.
Laszlo
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
