postgre ~模糊查询慢解决方式

工作中遇到个情况

sql如下：

SELECT org.id orgid,org."path" 
FROM (
    SELECT * FROM   A
    INNER JOIN t_org  org ON ...
    INNER JOIN C org ON ...
    INNER JOIN D b ON ...
    INNER JOIN E bsns ON ...
    LEFT JOIN F logopttp    ON  ...    
    LEFT JOIN G sysopttp    ON  ...
WHERE 1 = 1
   -- AND org.PATH ~ '(/177412/)';-- 加上这一行，时间12s，去掉之后0.3s,怀疑是因为模糊查询导致了全表扫描

解决方式：1.给需要查询快的字段加上gin索引

2. 用position函数，替代模糊查询

SELECT org.id orgid,org."path" 
FROM (
    SELECT * FROM   A
    INNER JOIN t_org  org ON ...
    INNER JOIN C org ON ...
    INNER JOIN D b ON ...
    INNER JOIN E bsns ON ...
    LEFT JOIN F logopttp    ON  ...    
    LEFT JOIN G sysopttp    ON  ...
WHERE 1 = 1
   AND position('/177412/' in org.path)>0；-- 这样的写法不导致全表扫描

 

转载于:https://www.cnblogs.com/xiaoliu66007/p/10858255.html