In response to my previous question about speed

I've got a speed problem for ya'll. Here's a sample of my query:
SELECT * FROM process_open_sockets AS pos LEFT JOIN (SELECT * FROM listening_ports) AS lp ON lp.port=pos.local_port AND lp.protocol=pos.protocol
I've got the subquery in there because I don't want to re-generate the listening_ports table for _every_ row in process_open_sockets. It used to run fast in an older version of osquery (not sure how old, it's been a while), but we just caught it taking 10+ minutes to run. The systems it's slow on have thousands of established connections.

Solved by doing the comparison in the WHERE clause instead of in the JOIN:

SELECT * FROM process_open_sockets AS pos WHERE (pos.local_port, pos.protocol) NOT IN (SELECT lp.port AS local_port, lp.protocol FROM listening_ports AS lp);