bug17213.sql

application/octet-stream

Download

DROP TABLE IF EXISTS issues, users, watchers;

CREATE TABLE issues (
    id int PRIMARY KEY
);

CREATE TABLE users (
    id int PRIMARY KEY
);

CREATE TABLE watchers (
    issue_id int,
    user_id int,

    CONSTRAINT unique_pair UNIQUE (issue_id, user_id)
);

CREATE INDEX watchers_user_idx ON watchers(user_id);

INSERT INTO issues (id) VALUES (1);
INSERT INTO issues (id) VALUES (2);
INSERT INTO issues (id) VALUES (3);
INSERT INTO issues (id) VALUES (4);

INSERT INTO users (id) VALUES (1);
INSERT INTO users (id) VALUES (2);

INSERT INTO watchers (issue_id, user_id) VALUES (1, 2);
INSERT INTO watchers (issue_id, user_id) VALUES (2, 1);
INSERT INTO watchers (issue_id, user_id) VALUES (3, 1);

analyze watchers;

EXPLAIN (ANALYZE)
SELECT i1.id FROM issues AS i1
WHERE EXISTS (
		SELECT
		FROM (
			SELECT i3.id
			FROM issues AS i3
			WHERE i3.id IN (
				SELECT w3.issue_id
				FROM (
					SELECT u1.id
					FROM users AS u1
					WHERE EXISTS (
						SELECT
						FROM watchers AS w1
						INNER JOIN users AS u2 ON (w1.user_id = u2.id)
						INNER JOIN watchers AS w2 ON (u1.id = w2.user_id)
						WHERE (i1.id = w1.issue_id)
						  AND (w2.issue_id != i1.id)
						  AND u1.id = u2.id)
                ) AS q
                INNER JOIN users AS superfluous ON (q.id = superfluous.id)
                INNER JOIN watchers AS w3 ON (q.id = w3.user_id)
            )
    ) AS q
);