--创建search_company CREATE MATERIALIZED VIEW search_company AS SELECT DISTINCT a.company_id, a.ticker, a.company_status_id, a.company_name_cn, a.company_name_en, a.company_shortname_cn, a.company_shortname_en, b.brand_name, a.year_founded, c.name_cn AS company_status_name_cn, c.name_en AS company_status_name_en FROM ((company_profile a LEFT JOIN brand_info b ON (((a.company_id)::text = (b.company_id)::text))) LEFT JOIN config.dictionary c ON (((a.company_status_id)::text = (c.id)::text))) WHERE a.company_status_id!='3.3' ORDER BY a.ticker; --导入自定义函数(扩展包) CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser); ALTER TEXT SEARCH CONFIGURATION zhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; create extension pg_trgm; create or replace function record_to_text(anyelement) returns text as $$ select $1::text; $$ language sql strict immutable; --创建全文索引 create index idx_search_company_gist on search_company using gist (record_to_text(search_company) gist_trgm_ops); create index idx_search_company_gin on search_company using gin (record_to_text(search_company) gin_trgm_ops); --检测索引是否正常 /*+ BitmapScan(search_company idx_search_company_gin) */ select * from search_company where record_to_text(search_company) ~ '经纬' and company_status_id like'3%' limit 99;