postgresql 搜索快速实现 全官方无第三方插件

Posted 19 months ago postgresql go linux search gse

直接分词 取词 然后 如下 在业务里直接分词不再pg里分词 轻量精简的搜索（效果还可以）

CREATE EXTENSION pg_trgm;

CREATE TABLE
  public.source_index (
    id serial NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    tagvector tsvector NULL
  );

ALTER TABLE
  public.source_index
ADD
  CONSTRAINT source_index_pkey PRIMARY KEY (id)
  
 CREATE INDEX source_index_gin_idx ON source_index USING GIN(tagvector);

select * from source_index;



insert into source_index (tagvector)  values(setweight(to_tsvector('simple', 'java 怎么写|javahome'), 'A')||setweight(to_tsvector('simple', 'aa|aaa'), 'B') || setweight(to_tsvector('simple', 'aa|1231'), 'C')) 


SELECT hc.id                     AS id,
       hc.created_at            AS createTime,
       ts_rank(hc.tagvector, query) AS score
FROM source_index hc,
     	 to_tsquery('simple', 'java怎么写') query
WHERE hc.tagvector @@ query
ORDER BY score DESC

点击评论