Message-ID: <1519399615.2530.14.camel@cybertec.at>
Subject: Re: need advice to tune postgresql
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Darius =?UTF-8?Q?P=C4=97=C5=BEa?= <darius@onrails.lt>,
	pgsql-performance@lists.postgresql.org
Date: Fri, 23 Feb 2018 16:26:55 +0100
In-Reply-To: 
 <CAGAb3f7sTq30vj1sDAH3BWBAh4CLV4OLa3GrdxW_iTSKBT3zfw@mail.gmail.com>
References: 
	<CAGAb3f7sTq30vj1sDAH3BWBAh4CLV4OLa3GrdxW_iTSKBT3zfw@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

Darius Pėža wrote:
> I have issue that update queries is slow, I need some advice how improve speed. I don't have much control to change queries. But I can change postresql server configuration
> 
> query example:
> 
> UPDATE "project_work" SET "left" = ("project_work"."left" + 2) WHERE ("project_work"."left" >= 8366)
> 
> sometimes updated lines count is up to 10k
> 
> postgresql version 9.3
> 
> postgresl.conf
> max_connections = 100			
> shared_buffers = 6GB			# min 128kB
> work_mem = 100MB				# min 64kB
> 
> all other values are default
> 
> server hardware
> Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz
> 16GB RAM
> disk is HDD
> 
> about half of resource I can dedicate for postgresql server.

If the number of updated lines is that big, you should try to
get HOT updates as much as possible.

For that, make sure that there is *no* index on the column,
and that the fillfactor for the table is suitably low (perhaps 50).

During a HOT update, when the new row version fits into the same
page as the old one, the indexes don't have to be updated.
That will speed up the UPDATE considerably.

On the other hand, an UPDATE like yours would then always use a
sequential scan, but that may still be a net win.

Other than that, setting checkpoint_segments high enough that
you don't get too many checkpoints can help.

Of course, more RAM and fast storage are always good.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com