Speed up new_updates query execution.

This makes no actual change to the query (the joins are all on the same
value, after all), but along with the removal of ms_current_timestamp in
the previous commit massively speeds it up as the query planner realises
it can do a hash join rather than a nested merge join.

1 files changed, 6 insertions, 5 deletions

diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
index 9bb1e81fe..d2264139a 100644
--- a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
@@ -30,17 +30,18 @@ sub email_alerts ($) {
                    $item_table.name as item_name, $item_table.anonymous as item_anonymous,
                    $item_table.confirmed as item_confirmed,
                    $head_table.*
-            from alert
-                inner join $item_table on alert.parameter::integer = $item_table.${head_table}_id
-                inner join $head_table on alert.parameter::integer = $head_table.id
+            from alert, $item_table, $head_table
+                where alert.parameter::integer = $head_table.id
+                and $item_table.${head_table}_id = $head_table.id
                 ";
         } else {
             $query .= " $item_table.*,
                    $item_table.id as item_id
-            from alert, $item_table";
+            from alert, $item_table
+            where 1 = 1";
         }
         $query .= "
-            where alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed
+            and alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed
             and $item_table.confirmed >= current_timestamp - '7 days'::interval
              and (select whenqueued from alert_sent where alert_sent.alert_id = alert.id and alert_sent.parameter::integer = $item_table.id) is null
             and $item_table.user_id <> alert.user_id