Re: GIN, pg_trgm and large table

Max Fomichev <max.fomitchev@gmail.com> writes:
> I have the following table and index with about 15 billion records.
> ...
> PostgreSQL 9.5.12 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
> 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
> ...
> It there any way to improve GIN/pg_tgrm performance on a such large table?

There was some work done in 9.6 to improve pg_trgm's performance when
dealing with queries involving very common trigrams.  So maybe an update
to 9.6 or v10 would help you.

I have a bad feeling though that 15 billion rows is too many for an index
based on trigrams to be really useful --- there are just not enough
distinct trigrams.  It's too bad we don't have a more general N-gram
indexing facility.

            regards, tom lane