- 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.
- 虛擬儀器設(shè)計(jì)測(cè)控應(yīng)用典型實(shí)例
- 計(jì)算機(jī)應(yīng)用
- Managing Mission:Critical Domains and DNS
- 精通Excel VBA
- 深度學(xué)習(xí)中的圖像分類與對(duì)抗技術(shù)
- JBoss ESB Beginner’s Guide
- Apache Spark Deep Learning Cookbook
- Visual C++編程全能詞典
- 可編程序控制器應(yīng)用實(shí)訓(xùn)(三菱機(jī)型)
- 網(wǎng)絡(luò)安全管理實(shí)踐
- Mastering Game Development with Unreal Engine 4(Second Edition)
- Mastering GitLab 12
- 手機(jī)游戲策劃設(shè)計(jì)
- FANUC工業(yè)機(jī)器人配置與編程技術(shù)
- 大數(shù)據(jù):引爆新的價(jià)值點(diǎn)