From bruce@momjian.us Sun May 24 04:56:31 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YtbLz-0007St-91 for pgsql-docs@arkaria.postgresql.org; Sat, 16 May 2015 12:38:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YtbLy-00028C-LR for pgsql-docs@arkaria.postgresql.org; Sat, 16 May 2015 12:38:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YtbLx-00027w-JE for pgsql-docs@postgresql.org; Sat, 16 May 2015 12:38:05 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1YtbLt-0001kd-R7 for pgsql-docs@postgresql.org; Sat, 16 May 2015 12:38:04 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1YtbLr-0001dH-UM for pgsql-docs@postgresql.org; Sat, 16 May 2015 08:37:59 -0400 Date: Sat, 16 May 2015 08:37:59 -0400 From: Bruce Momjian To: PostgreSQL-documentation Subject: Update docs for GIN index improvements Message-ID: <20150516123759.GB16100@momjian.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="LZvS9be/3tNcYl/X" Content-Disposition: inline User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --LZvS9be/3tNcYl/X Content-Type: text/plain; charset=us-ascii Content-Disposition: inline We have made major improvements in GIN in the past few releases, particularly 9.4, but the full text search docs recommending when to use GIN and GiST indexes have not been updated. This blog post empirically shows many of the improvements: http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-fulltext I would like to apply the attached patch to 9.5 to recommend GIN indexes for full text search, and remove mention of many old GIN limitations. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + --LZvS9be/3tNcYl/X Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="gin.diff" diff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml new file mode 100644 index b1c669f..538e6ad *** a/doc/src/sgml/textsearch.sgml --- b/doc/src/sgml/textsearch.sgml *************** SELECT plainto_tsquery('supernovae stars *** 3192,3198 **** ! GiST and GIN Index Types text search --- 3192,3198 ---- ! GIN and GiST Index Types text search *************** SELECT plainto_tsquery('supernovae stars *** 3213,3230 **** index ! GiST text search ! CREATE INDEX name ON table USING GIST (column); ! Creates a GiST (Generalized Search Tree)-based index. ! The column can be of tsvector or ! tsquery type. --- 3213,3229 ---- index ! GIN text search ! CREATE INDEX name ON table USING GIN (column); ! Creates a GIN (Generalized Inverted Index)-based index. ! The column must be of tsvector type. *************** SELECT plainto_tsquery('supernovae stars *** 3234,3250 **** index ! GIN text search ! CREATE INDEX name ON table USING GIN (column); ! Creates a GIN (Generalized Inverted Index)-based index. ! The column must be of tsvector type. --- 3233,3250 ---- index ! GiST text search ! CREATE INDEX name ON table USING GIST (column); ! Creates a GiST (Generalized Search Tree)-based index. ! The column can be of tsvector or ! tsquery type. *************** SELECT plainto_tsquery('supernovae stars *** 3253,3265 **** ! There are substantial performance differences between the two index types, ! so it is important to understand their characteristics. A GiST index is lossy, meaning that the index ! may produce false matches, and it is necessary to check the actual table row to eliminate such false matches. (PostgreSQL does this automatically when needed.) GiST indexes are lossy because each document is represented in the --- 3253,3270 ---- ! GIN indexes are the preferred text search index type. As inverted ! indexes, they contain an index entry for each word (lexeme), with a ! compressed list of matching locations. Multi-word searches can find ! the first match, then use the index to remove rows that are lacking ! additional words. GIN indexes store only the words (lexemes) of ! tsvector values, and not their weight labels. Thus a table ! row recheck is needed when using a query that involves weights. A GiST index is lossy, meaning that the index ! might produce false matches, and it is necessary to check the actual table row to eliminate such false matches. (PostgreSQL does this automatically when needed.) GiST indexes are lossy because each document is represented in the *************** SELECT plainto_tsquery('supernovae stars *** 3281,3333 **** - GIN indexes are not lossy for standard queries, but their performance - depends logarithmically on the number of unique words. - (However, GIN indexes store only the words (lexemes) of tsvector - values, and not their weight labels. Thus a table row recheck is needed - when using a query that involves weights.) - - - - In choosing which index type to use, GiST or GIN, consider these - performance differences: - - - - - 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 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 , while GiST index build time is not sensitive to that --- 3286,3291 ---- *************** SELECT plainto_tsquery('supernovae stars *** 3335,3341 **** ! 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 --- 3293,3299 ---- ! Partitioning of big collections and the proper use of GIN and GiST 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 --LZvS9be/3tNcYl/X Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs --LZvS9be/3tNcYl/X-- From bruce@momjian.us Sun May 24 04:56:31 2026 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Zj9jP-0006C6-Lr for pgsql-docs@arkaria.postgresql.org; Mon, 05 Oct 2015 17:39:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1Zj9jO-0006Uy-VA for pgsql-docs@arkaria.postgresql.org; Mon, 05 Oct 2015 17:39:22 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1Zj9j2-0005Rs-EJ for pgsql-docs@postgresql.org; Mon, 05 Oct 2015 17:39:00 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1Zj9iz-0005M8-Gp for pgsql-docs@postgresql.org; Mon, 05 Oct 2015 17:38:59 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1Zj9iy-000868-SD for pgsql-docs@postgresql.org; Mon, 05 Oct 2015 13:38:56 -0400 Date: Mon, 5 Oct 2015 13:38:56 -0400 From: Bruce Momjian To: PostgreSQL-documentation Subject: Re: Update docs for GIN index improvements Message-ID: <20151005173856.GA8770@momjian.us> References: <20150516123759.GB16100@momjian.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20150516123759.GB16100@momjian.us> User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org On Sat, May 16, 2015 at 08:37:59AM -0400, Bruce Momjian wrote: > We have made major improvements in GIN in the past few releases, > particularly 9.4, but the full text search docs recommending when to use > GIN and GiST indexes have not been updated. This blog post empirically > shows many of the improvements: > > http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-fulltext > > I would like to apply the attached patch to 9.5 to recommend GIN indexes > for full text search, and remove mention of many old GIN limitations. Done. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs