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

Here is the table:

                                   Unlogged table "public.huge_table"
   Column    |           Type           | Collation | Nullable |                    Default
-------------+--------------------------+-----------+----------+-----------------------------------------------
 id          | bigint                   |           | not null | nextval('huge_table_id_seq'::regclass)
 inserted_ts | timestamp with time zone |           |          | transaction_timestamp()
 value       | character varying        |           |          |
Indexes:
    "huge_table_pkey" PRIMARY KEY, btree (id)
    "huge_table_value_idx" UNIQUE, btree (value)
    "huge_table_value_trgm" gin (value gin_trgm_ops)

I managed to load the table initially in about 9 hours, after doing some
optimizations below based on various documentation (the server is 8-core Xeon
E5504, 16 GB RAM, 4 Hitachi 1TB 7200 RPM in a RAID 5 via Linux MD):
 ...

* maintenance_work_mem 512 MB

* synchronous_commit off

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
29578 postgres  20   0 6575672 6.149g 6.139g R  86.0 39.7  45:24.97 postgres

As for queries, doing a simple query like this one seems to require around 30
seconds to a minute. My volume is not crazy high but I am hoping I could get
this down to less than 30 seconds, because other stuff above this code will
start to time out otherwise:

osint=# explain analyze select * from huge_table where value ilike '%keyword%';