Question 89333 in PostgreSQL Question posted in
The official documentation can be found here.

SQL Filter Query Hangs with No Response – Postgresql

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

I’m trying to run the above query to get me all the user ids for users which have relationships with any of the other tables – so for example if a user has an order, or a userEmailCollection I would like their id to show up in the query output.

However, this query hangs without producing a response and I’m not sure why.

Tags:

3

Answers


  1. 0 
    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

    I would say the query can look something like this

    Login or Signup to reply.
  2. 0

    Echoing the above comments….be explicit in the join syntax and break the query into smaller sub queries. It’ll help the db to generate a more efficient execution plan. for example:

    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;

    Here the each cte joins the 2 tables and filters on specific conditions vs a cartesian join. using distinct will help remove the duplicate rows from the results set. It’s also a bit easier to read and follow.

    Login or Signup to reply.
  3. 0

    There’s no reason to join the tables because the query is about the presence of an association and not the attributes of the associations. The following query should return the desired result in reasonable time:

    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 is included in the subqueries to improve performance in case there are a large number of duplicate user IDs or emails in the associated tables. Because these are inclusion tests, not using DISTINCT will not change the result set.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search