Help Center > > Developer Guide> Full Text Search> Table and index> Constraints on Index Use

Constraints on Index Use

Updated at: Jul 15, 2020 GMT+08:00

The following is an example of index use:

create table table1 (c_int int,c_bigint bigint,c_varchar varchar,c_text text) with(orientation=row);

create text search configuration ts_conf_1(parser=POUND);
create text search configuration ts_conf_2(parser=POUND) with(split_flag='%');

set default_text_search_config='ts_conf_1';
create index idx1 on table1 using gin(to_tsvector(c_text));

set default_text_search_config='ts_conf_2';
create index idx2 on tscp_u_m_005_tbl using gin(to_tsvector(c_text));

select c_varchar,to_tsvector(c_varchar) from table1 where to_tsvector(c_text) @@ plainto_tsquery('¥#@...&**')   and to_tsvector(c_text) @@ plainto_tsquery('Company ')   and c_varchar is not null order by 1 desc limit 3;

In this example, table1 has two GIN indexes created on the same column c_text, idx1 and idx2, but these two indexes are created under different settings of default_text_search_config. Differences between this example and the scenario where one table has common indexes created on the same column are as follows:

  • GIN indexes use different parsers (that is, different delimiters). In this case, the index data of idx1 is different from that of idx2.
  • In the specified scenario, the index data of multiple common indexes created on the same column is the same.

As a result, using idx1 and idx2 for the same query returns different results.


Still use the above example. When:

  • Multiple GIN indexes are created on the same column of the same table.
  • The GIN indexes use different parsers (that is, different delimiters).
  • The column is used in a query, and an index scan is used in the execution plan.

    To avoid different query results caused by different GIN indexes, ensure that only one GIN index is available on a column of the physical table.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?

Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel