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: <56B84857-06EA-4534-A13F-F7AA3B924446@mhcomputing.net>
Date: Tue, 5 Dec 2017 22:15:13 -0800
Cc: pgsql-performance@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <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>
To: Jeff Janes <jeff.janes@gmail.com>,
 surrano@gmail.com
Precedence: bulk

On Nov 21, 2017, at 12:05 AM, Matthew Hall <mhall@mhcomputing.net> =
wrote:
>> Do you really need the artificial primary key, when you already have =
another column that would be used as the primary key?  If you need to =
use this it a foreign key in another type, then very well might.  But =
maintaining two unique indexes doesn't come free.
>=20
> OK, fair enough, I'll test with it removed and see what happens.

With the integer primary key removed, it still takes ~9 hours to load =
the table, so it didn't seem to make a big difference.

> Fixed it; I will re-test w/ 1 GB. Have you got any recommended values =
so I don't screw it up?

I also took this step for maintenance_work_mem.

Queries on the table still take a long time with the PK removed:

# explain (analyze, buffers) select * from huge_table where value ilike =
'%yahoo%';

 Bitmap Heap Scan on huge_table  (cost=3D593.72..68828.97 rows=3D18803 =
width=3D25) (actual time=3D3224.100..70059.839 rows=3D20909 loops=3D1)
   Recheck Cond: ((value)::text ~~* '%yahoo%'::text)
   Rows Removed by Index Recheck: 17
   Heap Blocks: exact=3D6682
   Buffers: shared hit=3D544 read=3D6760 dirtied=3D4034
   I/O Timings: read=3D69709.611
   ->  Bitmap Index Scan on huge_table_value_trgm_idx  =
(cost=3D0.00..589.02 rows=3D18803 width=3D0) (actual =
time=3D3216.545..3216.545 rows=3D20926 loops=3D1)
         Index Cond: ((value)::text ~~* '%yahoo%'::text)
         Buffers: shared hit=3D352 read=3D270
         I/O Timings: read=3D3171.872
 Planning time: 0.283 ms
 Execution time: 70065.157 ms
(12 rows)

The slow process during inserts is:

postgres: username dbname [local] INSERT

The slow statement example is:

2017-12-06 04:27:11 UTC [16085-10378] username@dbname LOG:  duration: =
5028.190 ms  plan:
        Query Text: INSERT INTO huge_table (value) VALUES
        .... 5000 values at once ...
        ON CONFLICT (value) DO NOTHING
        Insert on huge_table  (cost=3D0.00..75.00 rows=3D5000 width=3D40)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: huge_table_value_idx
          ->  Values Scan on "*VALUES*"  (cost=3D0.00..75.00 rows=3D5000 =
width=3D40)

> What is the size of the table and the gin index?

The table is 10 GB. The gin index is 5.8 GB.

> [=46rom Gabor Szucs] [H]ow about adding a hash value column and =
creating the unique index on that one? May block some false duplicates =
but the unique index would be way smaller, speeding up inserts.

The mean length of the input items is about 18 bytes. The max length of =
the input items is about 67 bytes. The size of the md5 would of course =
be 16 bytes. I'm testing it now, and I'll write another update.

Matthew.=