嵌套不包含在PostgreSQL中提高查询性能

卢卡斯·霍尔特（Lucas Holt）

我正在尝试将应用程序从MySQL 5.6移植到PostgreSQL 9.2，原始应用程序使用的视图已设法至少可以运行，但查询时间太长了。

我想知道PostgreSQL中优化“不在”查询的最佳方法。

我的第一个想法是创建一个临时表，但是由于这是一个视图，所以我认为这不是一个选择。

create VIEW ready_ports AS 
    SELECT ports.id AS id, 
           ports.run AS run,
           ports.name AS name, 
           ports.pkgname AS pkgname,
           ports.version AS version,
           ports.description AS description,
           ports.license AS license,
           ports.www AS www, 
           ports.status AS status, 
           ports.updated AS updated,
          (SELECT count(0) AS COUNT 
           FROM depends 
           WHERE depends.dependency = ports.id) AS priority 
    FROM ports 
    WHERE (ports.status = 'untested' and 
          (not(ports.id in 
                 (SELECT locks.port AS port 
                  FROM locks 
                  WHERE locks.port = ports.id)
               )
          ) and 
          (
             (not(ports.id in (SELECT depends.port AS port 
                               FROM depends 
                               WHERE depends.port = ports.id))) or 
             (not(ports.id in 
                  (SELECT depends.port AS port 
                   FROM depends
                   WHERE ((not(depends.dependency in 
                     (SELECT ports.id AS dep_id 
                      FROM ports   
                      WHERE (ports.id = depends.dependency 
                             and (ports.status = 'pass' 
                                  or ports.status = 'warn')
                             )
                     ))) or 
    depends.dependency in 
    (SELECT locks.port AS port 
     FROM locks 
     WHERE locks.port = ports.id)))))))
ORDER BY priority desc

                                                   查询计划                                                   
------------------------------------------------ -------------------------------------------------- --------------
 排序（cost = 367498265655.68..367498265763.29行= 43047宽度= 136）
   排序键：（（SubPlan 1））
   ->使用端口上的ports_1_idx进行索引扫描（cost = 0.00 ..367498259398.93行= 43047宽度= 136）
         索引条件：（（状态）::文本='未测试'::文本）
         过滤器：（（否（子计划2））和（（否（子计划3））或（否（子计划6））））子
         计划1- 
           >汇总（成本= 9.62..9.63行= 1宽度= 0）
                 ->仅使用depends_dependency_idx依赖于依赖进行索引扫描（成本= 0.00..9.47行= 60宽度= 0）
                       索引条件：（依赖关系= public.ports.id）
         SubPlan 2- 
           >仅对锁使用locks_port_key进行索引扫描（成本= 0.00 ..8.27行= 1宽度= 4）
                 索引条件：（端口= public.ports.id）子
         计划3- 
           >仅索引使用depends_pkey依赖于条件进行扫描（cost = 0.00..8.72行= 14宽度= 4）
                 索引条件： （端口= public.ports.id）子
         计划6- 
           >依序扫描取决于（成本= 8.27..6399946.81行= 1150079宽度= 4）
                 过滤器：（（不（子计划4））或（散列的子计划5））
                 SubPlan 4 
                   >使用端口上的ports_pkey进行索引扫描（cost = 0.00..8.31行= 1宽度= 4）
                         索引条件：（id = public.depends.dependency）
                         过滤器：（（（（status）:: text ='pass':: text）或（（status）:: text ='warn':: text））
                 SubPlan 5- 
                   >仅索引使用锁上的locks_port_key进行扫描（cost = 0.00 ..8.27行= 1宽度= 4）
                         索引条件：（端口= public.ports.id）

姿势

您可以尝试使用NOT EXISTS＆反联接版本，因为NOT IN不能使用很多索引（由于NULL处理问题）：

SELECT *
FROM table1
WHERE table1.id NOT IN (SELECT id FROM table2)

-- vs NOT EXISTS

SELECT *
FROM table1
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.id = table2.id)

-- vs anti-join

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
WHERE table2.id IS NULL

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。