CODEWITHSUNDEEP
postgresqlperformanceoptimizationsubquery
plenox
plenox

Reputation: 25

PostgreSQL - Optimize query with multiple subqueries

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?

Upvotes: 0

Views: 728

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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
            )
        )
    )
;

Upvotes: 1

Related Questions