From: | Laszlo Nagy <gandalf(at)shopzeus(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | Florian Weimer <fweimer(at)bfk(dot)de>, Daniel Fekete <danieleff(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Slow query + why bitmap index scan?? |
Date: | 2011-01-12 15:20:26 |
Message-ID: | 4D2DC6BA.3000400@shopzeus.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 2011-01-12 15:36, Kevin Grittner wrote:
> Laszlo Nagy<gandalf(at)shopzeus(dot)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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2011-01-12 16:31:50 | Re: Slow query + why bitmap index scan?? |
Previous Message | Kevin Grittner | 2011-01-12 14:36:55 | Re: Slow query + why bitmap index scan?? |