postgresql - 加快 PostgreSQL 中名称匹配的查询

Question

我正在尝试使用 mio 在大型地址表中进行名称搜索。的行。我有一个表地址和一个表管理员，可以找到从 1 级到 4 级的坐标。每当我在地址表中的地址字段中找到所有行政级别时，我想用管理表中的坐标更新地址表中的坐标。我也想为不同的行政级别组合做到这一点。例如，我创建了一个表 admin1_2 或 admin1_3，其中仅显示管理级别 1 和 2 或 1 和 3。为了确保每个管理员对都有唯一的坐标，我计算了所考虑的各个管理员级别的平均坐标。我想逐步实现匹配，从所有管理员级别开始，

问题是查询很慢。所有管理员级别需要超过 24 小时，而 3 个级别需要大约相同的时间。鉴于我有一堆不同的组合，这将需要很长时间。我试图适当地索引列，但没有足够的知识来理解查询计划以及如何使查询更快。任何帮助表示赞赏。请参阅下面的代码和查询计划。

create index country_idx on adress(country);
create index address_idx on address using GIN (address_field gin_trgm_ops);
update address t1 set
admin1 = t.admin1,
admin2 = t.admin2,
admin3 = t.admin3,
admin4 = t.admin4
postal_code = t.postal_code,
lat = t.lat,
lng = t.lng from (
select * from admin) t
where t1.country = t.country
and t1.address_field like '%' || t.admin1 || '%' --looks whether city name from admin shows up in address_field in t1
and t1.address_field like '%' || t.admin2 || '%'
and t1.address_field like '%' || t.admin3 || '%'
and t1.address_field like '%' || t.admin4 || '%'
;

Update on address t1  (cost=0.17..4069793.89 rows=134 width=203)"
  ->  Nested Loop  (cost=0.17..4069793.89 rows=134 width=203)"
        ->  Seq Scan on admin  (cost=0.00..24314.49 rows=958249     width=81)"
        ->  Bitmap Heap Scan on address t1  (cost=0.17..4.21 rows=1 width=73)"
              Recheck Cond: ((address_field ~~ (('%'::text || admin.admin1) || '%'::text)) AND (address_field ~~ (('%'::text || admin.admin2) || '%'::text)) AND (address_field ~~ (('%'::text || admin.admin3) || '%'::text)) AND (address_field ~~ (('%'::text || admin.admin4) || '%'::text)))"
              Filter: (admin.country = country_)"
              ->  Bitmap Index Scan on address_idx  (cost=0.00..0.17 rows=1 width=0)"
                    Index Cond: ((address_field ~~ (('%'::text || admin.admin1) || '%'::text)) AND (address_field ~~ (('%'::text || admin.admin2) || '%'::text)) AND (address_field ~~ (('%'::text || admin.admin3) || '%'::text)) AND (address_field ~~ (('%'::text || admin.admin4) || '%'::text)))"

编辑：管理字段使用 GIN 索引进行索引。我无法提高性能。有什么进一步的建议吗？

标签： postgresqlmatching