I have a query with 2 anti-joins (UserEmails = 1M+ rows and Subscriptions = <100k rows), 2 conditions, and a sort. I've created an index for the 2 conditions + sort, which sped up the query by 50%. Both anti-joins have indices. However, the query is too slow (4 seconds on production).
Here is the query:
SELECT
"Users"."firstName",
"Users"."lastName",
"Users"."email",
"Users"."id"
FROM
"Users"
WHERE
NOT EXISTS (
SELECT
1
FROM
"UserEmails"
WHERE
"UserEmails"."userId" = "Users". ID
)
AND NOT EXISTS (
SELECT
1
FROM
"Subscriptions"
WHERE
"Subscriptions"."userId" = "Users". ID
)
AND "isEmailVerified" = TRUE
AND "emailUnsubscribeDate" IS NULL
ORDER BY
"Users"."createdAt" DESC
LIMIT 100
Here is the explain:
Limit (cost=1.28..177.77 rows=100 width=49) (actual time=6171.121..6171.850 rows=100 loops=1)
-> Nested Loop Anti Join (cost=1.28..4665810.76 rows=2643614 width=49) (actual time=6171.119..6171.807 rows=100 loops=1)
-> Nested Loop Anti Join (cost=0.86..3470216.17 rows=2707688 width=49) (actual time=0.809..6062.152 rows=28607 loops=1)
-> Index Scan using users_email_subscribers_idx on "Users" (cost=0.43..1844686.50 rows=3312999 width=49) (actual time=0.055..2342.793 rows=1186607 loops=1)
-> Index Only Scan using "UserEmails_userId_emailId_key" on "UserEmails" (cost=0.43..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1186607)
Index Cond: ("userId" = "Users".id)
Heap Fetches: 1153034
-> Index Only Scan using "Subscriptions_userId_type_key" on "Subscriptions" (cost=0.42..0.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=28607)
Index Cond: ("userId" = "Users".id)
Heap Fetches: 28507
Planning time: 2.346 ms
Execution time: 6171.963 ms
And here is the index that improved the speed by 50%:
CREATE INDEX "users_email_subscribers_idx" ON "public"."Users" USING btree("createdAt" DESC) WHERE "isEmailVerified" = TRUE AND "emailUnsubscribeDate" IS NULL;
EDIT: I should also mention that the users_email_subscribers_idx is showing an Index Scan and not Index Only Scan likely because the index is being updated regularly.