PostgreSQL - Optimize query with multiple subqueries

Question

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?

Do you have an explain plan for the query? Performance tuning starts by understanding what the query is currently doing — Paul Maxwell
– Paul Maxwell, Commented Jan 6, 2019 at 22:58

Paul Maxwell · Accepted Answer · 2019-01-06 23:10:21Z

1

I would suggest, as a trial, to remove all grouping:

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
            )
        OR cookie_identifier IN (
            SELECT
                cookie_identifier
            FROM sessions
            WHERE user_id = 1234
            )
        )
    )
;

If that isn't helpful, try altering the above to use EXISTS instead of IN

SELECT
    *
FROM users u
WHERE EXISTS (
    SELECT
        NULL
    FROM sessions s
    WHERE s.user_id <> 1234
    AND u.id = s.user_id
    AND EXISTS (
        SELECT
            NULL
        FROM sessions s2
        WHERE s2.user_id = 1234
        AND (s.ip = s2.ip
          OR s.cookie_identifier = s2.cookie_identifier
            )
        )
    )
;

edited Jan 6, 2019 at 23:10

answered Jan 6, 2019 at 23:04

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - Optimize query with multiple subqueries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related