Laszlo Nagy <gandalf@shopzeus.com> 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.
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.
-Kevin