0

I have 2 tables, users and sessions. The tables look like this:

users - id (int), name (varchar)

sessions - id (int), user_id (int), ip (inet), cookie_identifier (varchar)

All columns have an index.

Now, I am trying to query all users that have a session with the same ip or cookie_identifier as a specific user.

Here is my query:

SELECT *
FROM "users"
WHERE "id" IN
    (SELECT "user_id"
     FROM "sessions"
     WHERE "user_id" <> 1234
       AND ("ip" IN
              (SELECT "ip"
               FROM "sessions"
               WHERE "user_id" = 1234
               GROUP BY "ip")
            OR "cookie_identifier" IN
              (SELECT "cookie_identifier"
               FROM "sessions"
               WHERE "user_id" = 1234
               GROUP BY "cookie_identifier"))
     GROUP BY "user_id")

The users table has ~200,000 rows, the sessions table has ~1.5 million rows. The query takes around 3-5 seconds.

Is it possible to optimize those results?

  • Please add index of each table. – McNets Jan 6 at 17:51

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.