Документ взят из кэша поисковой машины. Адрес
оригинального документа
: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/multi_column_index.html
Дата изменения: Unknown Дата индексирования: Sat Dec 22 10:29:16 2007 Кодировка: Поисковые слова: europa |
Previous | Next |
Adding a Multi-column Index That Includes A TSVECTOR ColumnThe 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.
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) |