Slow query: Watch notificationsettings

[johnaohara]

Describe the bug

The following query performs badly;

calls	total_exec_time	min_exec_time	max_exec_time	mean_exec_time
49	28512.408452	571.166768	619.233271	581.88588677551

WITH ens AS (
    SELECT ns.*, watch_id FROM notificationsettings ns
    JOIN watch_users wu ON NOT ns.isteam AND ns.name = wu.users
    
    UNION

    SELECT ns.*, watch_id FROM notificationsettings ns
    JOIN watch_teams wt ON ns.isteam AND ns.name = wt.teams

    UNION

    SELECT ns.*, watch_id FROM notificationsettings ns
    JOIN team t ON NOT ns.isteam
    AND ns.name = t.team_name
    JOIN watch_teams wt ON wt.teams = t.team_name
)
SELECT method, data, name
FROM ens
JOIN watch ON ens.watch_id = watch.id
WHERE testid = $1
AND name NOT IN (SELECT optout FROM watch_optout WHERE ens.watch_id  = watch_optout.watch_id)