数据库 jsonb字段gin索引对比GiST索引

原文： https://www.postgresql.org/docs/9.4/textsearch-indexes.html

• GIN index lookups are about three times faster than GiST

• GIN indexes take about three times longer to build than GiST

• GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled (see Section 58.4.1 for details)

• GIN indexes are two-to-three times larger than GiST indexes

As a rule of thumb, GIN indexes are best for static data because lookups are faster. For dynamic data, GiST indexes are faster to update. Specifically, GiST indexes are very good for dynamic data and fast if the number of unique words (lexemes) is under 100,000, while GIN indexes will handle 100,000+ lexemes better but are slower to update.

Note that GIN index build time can often be improved by increasing (https://www.postgresql.org/docs/9.4/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM), while GiST index build time is not sensitive to that parameter.

Partitioning of big collections and the proper use of GiST and GIN indexes allows the implementation of very fast searches with online update. Partitioning can be done at the database level using table inheritance, or by distributing documents over servers and collecting search results using the dblink module. The latter is possible because ranking functions use only local information.

---