Xrhrft

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.