Constraints on Index Use

Updated on 2024-09-30 GMT+08:00

View PDF

The following is an example of using an index. Run the following statements in a database that uses the UTF-8 or GBK encoding:

   
      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 table1 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.

Constraints

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.

Parent topic: Table and index

Previous topic: Creating an Index

Next topic: Controlling Text Search