PostgreSQL -使用多个子查询优化查询

Question

我有两张桌子，users和sessions。这些表格如下所示：

用户- id (int)，name (varchar)

会话- id (int)、user_id (int)、ip (Int)、cookie_identifier (varchar)

所有列都有索引。

现在，我正在尝试查询与特定用户具有相同ip或cookie_identifier会话的所有用户。

下面是我的问题：

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

users表有大约200,000行，sessions表有大约150,000行。查询大约需要3-5秒。

有可能优化这些结果吗？

Answer 1

作为试验，我建议删除所有分组：

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

如果这样做没有帮助，请尝试更改上面的代码，以使用EXISTS而不是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
            )
        )
    )
;