官术网_书友最值得收藏!

  • Mastering PostgreSQL 9.6
  • Hans Jurgen Schonig
  • 259字
  • 2021-07-09 19:57:23

Defining GIN indexes

If you want to apply text search to a column or a group of columns, there are basically two choices:

  • Create a functional index using GIN
  • Add a column containing ready to use tsvectors and a trigger to keep them in sync

In this section, both options will be outlines. To show how things work, I have created some sample data:

test=# CREATE TABLE t_fts AS SELECT comment FROM pg_available_extensions;
SELECT 43

Indexing the column directly with a functional index is definitely a slower but more space-efficient way to get things done:

test=# CREATE INDEX idx_fts_func ON t_fts USING gin(to_tsvector('english', comment)); 
CREATE INDEX

Deploying an index on the function is easy but it can lead to some overhead. Adding a materialized column needs more space but will lead to better runtime behavior:

test=# ALTER TABLE t_fts ADD COLUMN ts tsvector; 
ALTER TABLE

The only trouble is: how do you keep this column in sync? The answer is by using a trigger:

test=# CREATE TRIGGER tsvectorupdate  
BEFORE INSERT OR UPDATE ON t_fts
FOR EACH ROW
EXECUTE PROCEDURE
tsvector_update_trigger(somename, 'pg_catalog.english', "comment");

Fortunately, PostgreSQL already provides a C function that can be used by a trigger to sync the tsvector column. Just pass a name, the desired language, as well as a couple of columns to the function, and you are already done. The trigger function will take care of all that is needed. Note that a trigger will always operate within the same transaction as the statement making the modification. Therefore there is no risk of being inconsistent.

主站蜘蛛池模板: 辉南县| 阿巴嘎旗| 莆田市| 通州区| 建阳市| 手游| 贵州省| 来宾市| 千阳县| 昌邑市| 阳信县| 合水县| 长春市| 蓬安县| 兴和县| 八宿县| 塘沽区| 金沙县| 青河县| 南靖县| 固始县| 邯郸市| 汪清县| 神农架林区| 乐平市| 嵊泗县| 临漳县| 安化县| 峡江县| 尼玛县| 阿拉善左旗| 洞口县| 康马县| 西昌市| 紫金县| 宁德市| 舒城县| 都兰县| 大洼县| 乌拉特后旗| 阜平县|