I'm following you... but even where it's more a "real" exists like
select u.user_id, last_name, email, priv_email, url
from users u
where exists (select 1 from bf2_party_stats_mv p
where p.user_id = u.user_id and score > 1)
(note each user can have multiple parties)
it's a factor of 10 or so slower than
select u.user_id, last_name, email, priv_email, url
from users u, bf2_party_stats_mv p
where u.user_id = p.user_id
group by u.user_id, last_name, email, priv_email, url
having sum(score) > 1
Here, party_stats_mv is extremely volatile so I have no indexes on it and the double sequential scan PG does with the exists query kills it. However, similar queries on tables where I can have an index on the join key show the exists form to be faster by 50-100%. But, the exists form is about 20% slower for count(*) queries whether or not an index exists.
(Tested with 1.2k rows in users and 1.6k in parties.)
