Re: insert and query performance on big string table with pg_trgm

> On Dec 5, 2017, at 11:23 PM, Sergei Kornilov  wrote:
> 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

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.