Adding a Multi-column Index That Includes A TSVECTOR Column

The scope of a search can be reduced and search times improved by adding where conditions on a tsvector column together with a where condition on another column. To be fast, both where conditions should use an index. To create a multicolumn index, both column must use the same index type. In this case, they will both use a gist index.

• contrib/btree_gist implements a btree index using GiST and supports btree operators '=', '<', '<=', '>', '>=' for int2, int4, int8, float4, float8, and timestamp data types.

Install contrib/btree_gist. Go to the root of your source tree where you ran configure for PostgreSQL.

cd contrib/btree_gist
gmake
su
gmake install
exit

Add btree_gist to the ts_db database.

psql ts_db < /usr/local/pgsql/share/contrib/btree_gist.sql

Start a psql session.

psql ts_db

CREATE INDEX postgresql_manual_chapter_body_in 
ON postgresql_manual 
USING GIST (chapter, ts_vec);

CREATE INDEX

vacuum full analyze;

VACUUM

With a multicolumn index.

EXPLAIN ANALYZE 
SELECT * 
FROM postgresql_manual 
WHERE ts_vec @@ to_tsquery('default_english', 'create & schema') and chapter = 5;

                           QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using postgresql_manual_chapter_body_in on postgresql_manual  
 (cost=0.00..3.50 rows=1 width=143) (actual time=0.150..0.387 rows=5 loops=1)
   Index Cond: ((chapter = 5) AND (ts_vec @@ '\'create\' & \'schema\''::tsquery))
   Filter: (ts_vec @@ '\'create\' & \'schema\''::tsquery)
 Total runtime: 0.635 ms
(4 rows)

Without a multicolumn index.

drop index postgresql_manual_chapter_body_in;

DROP INDEX

EXPLAIN ANALYZE 
SELECT * 
FROM postgresql_manual 
WHERE ts_vec @@ to_tsquery('default_english', 'create & schema') and chapter = 5;
                                                                  
                           QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using postgresql_manual_body_in on postgresql_manual  
 (cost=0.00..3.50 rows=1 width=143) (actual time=0.342..0.895 rows=5 loops=1)
   Index Cond: (ts_vec @@ '\'create\' & \'schema\''::tsquery)
   Filter: ((chapter = 5) AND (ts_vec @@ '\'create\' & \'schema\''::tsquery))
 Total runtime: 0.958 ms
(4 rows)