Message-ID: <4D2DC6BA.3000400@shopzeus.com>
Date: Wed, 12 Jan 2011 16:20:26 +0100
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: Florian Weimer <fweimer@bfk.de>, Daniel Fekete <danieleff@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow query + why bitmap index scan??
References: <4D2D9B1E.7080302@shopzeus.com> <82y66qp7al.fsf@mid.bfk.de>
	<4D2DB8F9.9050609@shopzeus.com>
	<4D2D68270200002500039327@gw.wicourts.gov>
In-Reply-To: <4D2D68270200002500039327@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2011-01-12 15:36, Kevin Grittner wrote:
> 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.
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