select u.id
from users u
join orders ord on u.id=ord."orderUserId"
where ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6) 
union
select u.id
from users u
join "userEmailCollections" col on u.id=col."userId"
where col."restaurantId" = 6 
union 
select u.id
from users u
join "reviewTracks" rev on u.email=rev."email"
where rev."restaurantId" = 6 
union 
select u.id
from users u
join "loyaltyCampaignRedemptions" loyy on u.id=loyy."userId"
where loyy."restaurantId" = 6

我会说查询可以看起来像这样

没有理由连接表，因为查询是关于关联的存在而不是关联的属性。下面的查询应该在合理的时间内返回所需的结果：

SELECT users.id
  FROM users
 WHERE users.id IN
         (SELECT DISTINCT orders."orderUserId"
           FROM orders
          WHERE orders."restaurantTableId" IN (12, 7, 9, 8, 10, 11, 14, 99, 100, 6))
    OR users.id IN
         (SELECT DISTINCT "userEmailCollections"."userId"
            FROM "userEmailCollections"
           WHERE "userEmailCollections"."restaurantId" = 6)
    OR users.email IN
         (SELECT DISTINCT "reviewTracks"."email"
            FROM "reviewTracks"
           WHERE "reviewTracks"."restaurantId" = 6)
    OR users.id IN
         (SELECT DISTINCT "loyaltyCampaignRedemptions"."userId"
            FROM "loyaltyCampaignRedemptions"
           WHERE "loyaltyCampaignRedemptions"."restaurantId" = 6);

DISTINCT包含在子查询中，以便在关联表中存在大量重复的用户ID或电子邮件时提高性能。由于这些是包含测试，因此不使用DISTINCT不会更改结果集。

回应上面的评论....在join语法中显式，并将查询分解为更小的子查询。这将有助于数据库生成更有效的执行计划。例如：

SELECT DISTINCT users.id
FROM users
LEFT JOIN orders ON users.id = orders."orderUserId" AND orders."restaurantTableId" IN (12, 7, 9, 8, 10, 11, 14, 99, 100, 6)
LEFT JOIN "userEmailCollections" ON "userEmailCollections"."userId" = users.id AND "userEmailCollections"."restaurantId" = 6
LEFT JOIN "reviewTracks" ON users.email = "reviewTracks"."email" AND "reviewTracks"."restaurantId" = 6
LEFT JOIN "loyaltyCampaignRedemptions" ON "users".id = "loyaltyCampaignRedemptions"."userId" AND "loyaltyCampaignRedemptions"."restaurantId" = 6;
WHERE orders."orderUserId" IS NOT NULL OR "userEmailCollections"."userId" IS NOT NULL OR "reviewTracks"."email" IS NOT NULL OR "loyaltyCampaignRedemptions"."userId" IS NOT NULL;

这里的each cte连接两个表，并在特定条件下过滤，而不是笛卡尔连接。使用distinct将有助于从结果集中删除重复的行。它也更容易阅读和理解。