From: Matthew Hall <mhall@mhcomputing.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: insert and query performance on big string table with pg_trgm
Message-Id: <F5E9C927-1F84-488B-BD02-01DAFDD369D9@mhcomputing.net>
Date: Mon, 20 Nov 2017 14:54:01 -0800
To: pgsql-performance@lists.postgresql.org
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Precedence: bulk

Hello PGSQL experts,

I've used your great database pretty heavily for the last 4 years, and =
during
that time it's helped me to solve an amazingly wide variety of data
challenges. Last week, I finally ran into something weird enough I =
couldn't
figure it out by myself. I'm using a self-compiled copy from latest 10.x
stable branch, Ubuntu 16.04 LTS, inserts with psycopg2, queries (so far) =
with
psql for testing, later JDBC (PostgreSQL 10.1 on x86_64-pc-linux-gnu, =
compiled
by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.5) 5.4.0 20160609, 64-bit).

I have this great big table of strings, about 180 million rows. I want =
to be
able to search this table for substring matches and overall string =
similarity
against various inputs (an ideal use case for pg_trgm, from what I can =
see in
the docs and the research articles for such indexing).

I need a unique b-tree index on the strings, to prevent duplicates in =
the
input in the beginning, and from adding new strings in the future, and =
the
{gin,gist}_trgm_ops index to speed up the matching. I couldn't fully
understand from the docs if my use case was a better fit for GIN, or for =
GIST.
Some parts of the docs implied GIST would be faster, but only for less =
than
100K entries, at which point GIN would be faster. I am hoping someone =
could
comment.

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):

* compiled latest 10.x stable code branch from Git
* unlogged table (risky but made a big difference)
* shared_buffers 6 GB
* work_mem 32 MB
* maintenance_work_mem 512 MB
* effective_cache_size 10 GB
* synchronous_commit off
* wal_buffers 16 MB
* max_wal_size 4 GB
* checkpoint_completion_target 0.9
* auto_explain, and slow log for >=3D 1000 msecs (to debug this)

I'm noticing that the performance of inserts starts slipping quite a =
bit, as
the data is growing. It starts out fast, <1 sec per batch of 5000, but
eventually slows to 5-10 sec. per batch, sometimes randomly more.

In this example, it was just starting to slow, taking 4 secs to insert =
5000
values:

2017-11-18 08:10:21 UTC [29578-11250] arceo@osint LOG:  duration: =
4034.901 ms  plan:
        Query Text: INSERT INTO huge_table (value) VALUES
        ('value1'),
	... 4998 more values ...
        ('value5000')
        ON CONFLICT (value) DO NOTHING
        Insert on huge_table  (cost=3D0.00..87.50 rows=3D5000 width=3D48)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: huge_table_value_idx
          ->  Values Scan on "*VALUES*"  (cost=3D0.00..87.50 rows=3D5000 =
width=3D48)

When it's inserting, oddly enough, the postgres seems mostly CPU =
limited,
where I would have expected more of an IO limit personally, and the =
memory
isn't necessarily over-utilized either, so it makes me wonder if I =
missed some
things.

KiB Mem : 16232816 total,   159196 free,   487392 used, 15586228 =
buff/cache
KiB Swap: 93702144 total, 93382320 free,   319816 used.  8714944 avail =
Mem

  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=3D# explain analyze select * from huge_table where value ilike =
'%keyword%';
                                                                     =
QUERY PLAN
=
--------------------------------------------------------------------------=
--------------------------------------------------------------------------=
-
 Bitmap Heap Scan on huge_table  (cost=3D273.44..61690.09 rows=3D16702 =
width=3D33) (actual time=3D2897.847..58438.545 rows=3D16423 loops=3D1)
   Recheck Cond: ((value)::text ~~* '%keyword%'::text)
   Rows Removed by Index Recheck: 3
   Heap Blocks: exact=3D5954
   ->  Bitmap Index Scan on huge_table_value_trgm  (cost=3D0.00..269.26 =
rows=3D16702 width=3D0) (actual time=3D2888.846..2888.846 rows=3D16434 =
loops=3D1)
         Index Cond: ((value)::text ~~* '%keyword%'::text)
 Planning time: 0.252 ms
 Execution time: 58442.413 ms
(8 rows)

Thanks for reading this and letting me know any recommendations.

Sincerely,
Matthew Hall=