Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1eMU3C-0004pN-2X
	for pgsql-performance@arkaria.postgresql.org; Wed, 06 Dec 2017 07:23:26 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1eMU3B-0007zP-Bl
	for pgsql-performance@arkaria.postgresql.org; Wed, 06 Dec 2017 07:23:25 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <sk@zsrv.org>)
	id 1eMU3A-0007zA-WE
	for pgsql-performance@lists.postgresql.org; Wed, 06 Dec 2017 07:23:25 +0000
Received: from forward2j.cmail.yandex.net ([5.255.227.20])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <sk@zsrv.org>)
	id 1eMU32-0007XT-IH
	for pgsql-performance@lists.postgresql.org; Wed, 06 Dec 2017 07:23:23 +0000
Received: from mxback7o.mail.yandex.net (mxback7o.mail.yandex.net
 [IPv6:2a02:6b8:0:1a2d::21])
	by forward2j.cmail.yandex.net (Yandex) with ESMTP id BA60A217F4;
	Wed,  6 Dec 2017 10:23:11 +0300 (MSK)
Received: from web43j.yandex.ru (web43j.yandex.ru [2a02:6b8:0:1619::343])
	by mxback7o.mail.yandex.net (nwsmtp/Yandex) with ESMTP id
 KNV54LZSRZ-NAk0rr0c;
	Wed, 06 Dec 2017 10:23:10 +0300
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zsrv.org; s=mail;
 t=1512544990;
	bh=vU3KNeW6dLKj1zQ7lHXRKNQCVph9h6isS1MwrntDgBs=;
	h=From:To:Cc:In-Reply-To:References:Subject:Message-Id:Date;
	b=R2w52Wsx9QGhHflobGCaQ1cr4QC0yc3/9PQ8FmWMsyGavX1zlAe9MXk1z0CFfbZdo
	 nZmQ0+Bwrlz7Q/3gTEpNMH/I33klyBWgyFOXQY77mCMNu/07y9LNDdCN/1wnslDQ6x
	 7Q14dhbxke5jWUifRdWWn2KLjKzJ1reSvYMf/5Fc=
Authentication-Results: mxback7o.mail.yandex.net; dkim=pass header.i=@zsrv.org
Received: by web43j.yandex.ru with HTTP;
	Wed, 06 Dec 2017 10:23:10 +0300
From: Sergei Kornilov <sk@zsrv.org>
To: Matthew Hall <mhall@mhcomputing.net>,
	Jeff Janes <jeff.janes@gmail.com>,
	"surrano@gmail.com" <surrano@gmail.com>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
In-Reply-To: <351E3FBB-7DFC-4BB0-841E-E7AB16DD57A3@mhcomputing.net>
References: <F5E9C927-1F84-488B-BD02-01DAFDD369D9@mhcomputing.net>
 <CAMkU=1yPVyiyyXOxDyDCA18zqaorkoZrfns-2CZXvGi5uZw8xQ@mail.gmail.com>
 <56B84857-06EA-4534-A13F-F7AA3B924446@mhcomputing.net>
 <351E3FBB-7DFC-4BB0-841E-E7AB16DD57A3@mhcomputing.net>
Subject: Re: insert and query performance on big string table with pg_trgm
MIME-Version: 1.0
Message-Id: <313361512544990@web43j.yandex.ru>
X-Mailer: Yamail [ http://yandex.ru ] 5.0
Date: Wed, 06 Dec 2017 10:23:10 +0300
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

>    Buffers: shared hit=544 read=6760 dirtied=4034
>    I/O Timings: read=69709.611
You has very slow (or busy) disks, not postgresql issue. Reading 6760 * 8KB in 70 seconds is very bad result.

For better performance you need better disks, at least raid10 (not raid5). Much more memory in shared_buffers can help with read performance and so reduce disk utilization, but write operations still will be slow.

Sergei