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:

FROM "users"
    (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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.