Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: insert and query performance on big string table with pg_trgm
From: Matthew Hall <mhall@mhcomputing.net>
In-Reply-To: <313361512544990@web43j.yandex.ru>
Date: Wed, 6 Dec 2017 18:04:54 -0800
Cc: Jeff Janes <jeff.janes@gmail.com>,
 "surrano@gmail.com" <surrano@gmail.com>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <8C8D9D8C-F0C6-48D3-AE26-B733EFAED4DB@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>
 <313361512544990@web43j.yandex.ru>
To: Sergei Kornilov <sk@zsrv.org>
Precedence: bulk


> On Dec 5, 2017, at 11:23 PM, Sergei Kornilov <sk@zsrv.org> wrote:
> You has very slow (or busy) disks, not postgresql issue. Reading 6760 =
* 8KB in 70 seconds is very bad result.
>=20
> 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.
>=20
> Sergei

Sergei,

Thanks so much for confirming, this really helps a lot to know what to =
do. I thought the disk could be some of my issue, but I wanted to make =
sure I did all of the obvious tuning first. I have learned some very =
valuable things which I'll be able to use on future challenges like this =
which I didn't learn previously.

Based on this advice from everyone, I'm setting up a box with more RAM, =
lots of SSDs, and RAID 10. I'll write back in a few more days after I've =
completed it.

I can also confirm that the previous advice about using a hash / digest =
based unique index seemed to make the loading process slower for me, not =
faster, which is an interesting result to consider for future users =
following this thread (if any). I don't yet have specific data how much =
slower, because it's actually still going!

Sincerely,
Matthew.=