Working with Indexes - PostgreSQL Developer's Guide - page 73

Now, create a GIN index on the table, execute the same query again, and note down
the timings in the following manner:

warehouse_db=# CREATE INDEX words_idx ON words USING gin
(simple_words gin_trgm_ops, special_words
gin_trgm_ops);
CREATE INDEX
Time: 142836.712 ms

The result can be seen using the following statement:

warehousd_db=# SELECT count(*) FROM words WHERE simple_words LIKE
'%a31%' AND special_words LIKE '%a31%';
count
-------
116
(1 row)
Time: 7.105 ms

Now from the following table, we can clearly see the performance improvement
using the GIN index:

Time without an index Time with multicolumn index Time with the GIN Index

1081.796 ms 1075.467 ms 7.105 ms

More details on the GIN index can be found at http://www.sai.msu.
su/~megera/wiki/Gin .
More details on pg_trgm and gin_trgm_ops can be found at
http://www.postgresql.org/docs/9.4/static/pgtrgm.html .

Index bloating
As the architecture of PostgreSQL is based on MVCC, tables have the dificulty of
dead rows. Rows that are not visible to any transaction are considered dead rows .
In a continuous table, some rows are deleted or updated. These operations cause
dead space in a table. Dead space can potentially be reused when new data is
inserted. Due to a lot of dead rows, bloating occurs. There are various reasons for
index bloating, and it needs to be ixed to achieve more performance, because it
hurts the performance of the database. AUTO VACUUM is the best obviation from
bloating, but it is a conigurable parameter and can be incapacitated or erroneously
conigured. There are multiple ways to ix index bloating; hence, we will discuss the
ways to avert this and their eficacy in the following sections.