Here's a simplification of the problem... suppose you have a bunch of socks in a drawer. Each sock has an id associated with it, but they might be different striped colors. Let's say the stripes are stored as different entries in a second table.. so for each sock with a sockid (left table) there's a stripe=red entry in table 2 and there's a stripe=blue entry in table 2 but there might be stripe=green or stripe=yellow in table 2 The 2 tables are joined by a sockid numeric identifier (indexed)
Now, let's say there are 10s or 100s of millions of socks (okay, it's a really big drawer) and you want to find all of the sockids that have a red stripe but not a blue stripe. We don't care if there are other stripes, but it must have a red stripe and it must not have a blue stripe.
Now, this is a simplification of a different schema and structure, but let's stipulate that it is currently not possible to adjust the database schema or normalize, and that there are valid reasons to arrange it this way even though there are no socks or stripes in reality, but they are representative of something that is more complicated to explain and the explanation would distract from the crux of the issue. Hence, the simplification down to stripes on socks, which is easier to understand and visualize.
I could do a construction (CTE or subselect or whatever - this is PG13 btw) where I construct a temp table with all of the socks with red stripes and all of the socks with blue stripes and then left join the two temporary tables together and omit the things that fall in both. It's straightforward to imagine and construct this query, but it could result in enormous temp tables that may both be the entire size of the original table (we have 10s of millions of socks with red stripes and are partial to red+blue stripes such that there is somebody that identifies all of the socks with red stripes without blue stripes and adds a blue stripe when one does not exist, as a separate process. All socks with red stripes but not blue stripes are constantly being updated to have blue stripes, and we must constantly search for those that and add the blue stripe.
I could also try to keep all of the blue strips in a massive array and then sockid not in blue_array, but the size of the array seems like it would also be inefficient to search for millions in a loop.
More [presumed] memory efficient than that would be to make an array_agg of the sock stripes for every sock in a lateral join, something like:
select sock.id from socks, lateral ( select array_agg(stripes.color) as arr from stripes join socks on socks.id = stripes.sock_id ) as sock_stripes where sock_stripes.arr @> array['red'] and not sock_stripes.arr @> array['blue']
Any thoughts on scaling this up? (an important note is that after the first run of this, it's potentially possible to leverage another field that indicates the time of the last check of a socket and look for only red-striped socks that are added after that time, but that's an optimization that doesn't help us the first time)
I hope this makes sense.