tsearch performance
От | Chantal Ackermann |
---|---|
Тема | tsearch performance |
Дата | |
Msg-id | 3E5E10F2.2000907@biomax.de обсуждение исходный текст |
Список | pgsql-performance |
hi all, we have a tsearch index on the following table: Table "public.sentences" Column | Type | Modifiers ---------------+---------+-------------------- sentence_id | bigint | not null puid | integer | py | integer | journal_id | integer | sentence_pos | integer | not null sentence_type | integer | not null default 0 sentence | text | not null sentenceidx | txtidx | not null Indexes: sentences_pkey primary key btree (sentence_id), sentence_uni unique btree (puid, sentence_pos, sentence), sentenceidx_i gist (sentenceidx), sentences_puid_i btree (puid), sentences_py_i btree (py) the table contains 50.554.768 rows and is vacuum full analyzed. The sentenceidx has been filled NOT USING txt2txtidx, but a custom implementation that should have had the same effect (parsing into words/phrases, deleting stop words). Nevertheless, might this be the reason for the very bad performance of the index, or is the table "just" to big (I hope not!)? Note that the index on sentenceidx has not been clustered, yet. I wanted to ask first whether I might need to refill the column sentenceidx using txt2txtidx. (with so many rows every action has to be reconsidered ;-) ) EXPLAIN ANALYZE SELECT sentence FROM sentences WHERE sentenceidx @@ 'amino\\ acid'; QUERY PLAN ------------------------------------------------------------------- Index Scan using sentenceidx_i on sentences (cost=0.00..201327.85 rows=50555 width=148) (actual time=973940.41..973940.41 rows=0 loops=1) Index Cond: (sentenceidx @@ '\'amino acid\''::query_txt) Filter: (sentenceidx @@ '\'amino acid\''::query_txt) Total runtime: 973941.09 msec (4 rows) thank you for any thoughts, hints, tips! Chantal
В списке pgsql-performance по дате отправления: