Improving query performance in PostgreSQL with nested not in

Question

I'm attempting to port an application from MySQL 5.6 to PostgreSQL 9.2 The original application uses a view that I've managed to get to at least run but the query time is horrible.

I want to know the best approach in PostgreSQL to optimize "not in" queries.

My first thought was to create a temp table, but as this is a view, I don't think it's an option.

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

                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Sort  (cost=367498265655.68..367498265763.29 rows=43047 width=136)
   Sort Key: ((SubPlan 1))
   ->  Index Scan using ports_1_idx on ports  (cost=0.00..367498259398.93 rows=43047 width=136)
         Index Cond: ((status)::text = 'untested'::text)
         Filter: ((NOT (SubPlan 2)) AND ((NOT (SubPlan 3)) OR (NOT (SubPlan 6))))
         SubPlan 1
           ->  Aggregate  (cost=9.62..9.63 rows=1 width=0)
                 ->  Index Only Scan using depends_dependency_idx on depends  (cost=0.00..9.47 rows=60 width=0)
                       Index Cond: (dependency = public.ports.id)
         SubPlan 2
           ->  Index Only Scan using locks_port_key on locks  (cost=0.00..8.27 rows=1 width=4)
                 Index Cond: (port = public.ports.id)
         SubPlan 3
           ->  Index Only Scan using depends_pkey on depends  (cost=0.00..8.72 rows=14 width=4)
                 Index Cond: (port = public.ports.id)
         SubPlan 6
           ->  Seq Scan on depends  (cost=8.27..6399946.81 rows=1150079 width=4)
                 Filter: ((NOT (SubPlan 4)) OR (hashed SubPlan 5))
                 SubPlan 4
                   ->  Index Scan using ports_pkey on ports  (cost=0.00..8.31 rows=1 width=4)
                         Index Cond: (id = public.depends.dependency)
                         Filter: (((status)::text = 'pass'::text) OR ((status)::text = 'warn'::text))
                 SubPlan 5
                   ->  Index Only Scan using locks_port_key on locks  (cost=0.00..8.27 rows=1 width=4)
                         Index Cond: (port = public.ports.id)

@rob: like it is now is fine -- or at least reasonable. See what it looked like when it was initially posted: a giant blob of inlined subqueries. — Denis de Bernardy, Apr 07 '14 at 21:56
@Denis Personally looking at the history I think that the previous version was easier to read and this one has too much white space. This is an area where there isn't any such thing as "properly". — rjzii, Apr 07 '14 at 22:52

score 4 · Accepted Answer · edited May 23 '17 at 12:33

You could try the NOT EXISTS & anti-join versions, as NOT IN cannot use much of your indexes (because of NULL handling issues):

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

score 2 · Answer 2 · answered Apr 08 '14 at 00:09

I ended up using a combination of joins and not exists queries to get a final working query.

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 
LEFT JOIN locks on locks.port = ports.id
LEFT JOIN depends on depends.port = ports.id
WHERE ports.status = 'untested' and locks.id is null and 
      (depends.port is null or 
         not exists
              (SELECT depends.port AS port 
               FROM depends WHERE ports.id = depends.port and not exists              
                 (SELECT ports.id as dep_id
                  FROM ports   
                  WHERE ports.id = depends.dependency and 
                  (ports.status = 'pass' or ports.status = 'warn'))
                  or 
depends.dependency = locks.port))
ORDER BY priority desc, ports.name asc

score 1 · Answer 3 · answered Apr 07 '14 at 22:12

You need to rewrite the query using joins:

select ...
from ports
left join locks ...
left join depends ...
where criteria

That way, you'll be working on one big set, which is the result of three sets, instead of working on a half dozen sets.

Moving the count out of your view will be a plus as well. Use a seperate query or join your view to get that part. (An aggregate in a view is rarely a good idea, except perhaps in reports.)

Improving query performance in PostgreSQL with nested not in

3 Answers3