Date: Fri, 9 Jun 2006 10:54:02 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Andrus <eetasoft@online.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why date index is not used
Message-ID: <20060609155402.GW45331@pervasive.com>
References: <44887875.8000502@fuzzy.cz>
	<01de01c68b37$33e22a80$8300a8c0@tridecap.com>
	<e6bgd4$1vgk$2@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <e6bgd4$1vgk$2@news.hub.org>
User-Agent: Mutt/1.5.11

On Fri, Jun 09, 2006 at 12:40:26PM +0300, Andrus wrote:
> > Actually It looks to me like the sorting is the slow part of this query.
> > Maybe if you did create an index on both kuupaev and kellaaeg it might
> > make the sorting faster.
> 
> Thank you. It makes query fast.
> 
> > Or maybe you could try increasing the server's
> > work mem.  The sort will be much slower if the server can't do the whole
> > thing in ram.
> 
> I have W2K server with 0.5 GB RAM
> there are only 6 connections open ( 6 point of sales) to this server.
> shared_buffes is 10000
> I see approx 10 postgres processes in task manager each taking about 30 MB
> ram
> 
> Server prefomance is very slow: Windows swap file size is 1 GB
> 
> For each sale a new row will be inserted to this table. So the file size
> grows rapidly every day.
> Changing work_mem by 1 MB  increares memory requirment by 10 MB since I may
> have 10 processes running. Sorting in memory this table requires very large
> amout of work_mem for each process address space.
> 
> I think that if I increase work_mem  then swap file will became bigger and
> perfomance will decrease even more.
> 
> How to increase perfomance ?

Do you have effective_cache_size set correctly? You might try dropping
random_page_cost down to 2 or so.

Of course you could just put more memory in the machine, too.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461