Make a query capable of using an index.

Postgres doesn't choose the optimal plan, but we're still down from
100 to 10 ms.

diff --git a/bin/sync.pl b/bin/sync.pl
index 2d315c9d2a646f7448d82b03db5f65b946f02ff4..b725b6ef383948c599a9dde60382528fcd4cfef3 100644 (file)
--- a/bin/sync.pl
+++ b/bin/sync.pl
@@ -374,15 +374,15 @@ sub create_move_log {
         my $q = $dbh->prepare(<<"EOF");
 SELECT
   name, g_old.group_name as old_group, g_new.group_name as new_group, TO_CHAR(g_new.change_seen, \'YYYY-mm-dd HH24:MI\') AS change_seen
-FROM ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_old
-  FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_new USING (name)
+FROM ( SELECT * FROM current_group_membership_history WHERE channel=? AND ts=? ) g_old
+  FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE channel=? AND ts=? ) g_new USING (name)
 WHERE
   g_new.group_name IS DISTINCT FROM g_old.group_name
   AND g_new.group_name IS NOT NULL
 ORDER BY g_new.change_seen DESC, name
 LIMIT 50
 EOF
-       $q->execute($prev_invitation_ts, $invitation_ts);
+       $q->execute($config::invitation_channel, $prev_invitation_ts, $config::invitation_channel, $invitation_ts);
        my @recent_moves = ();
        while (my $ref = $q->fetchrow_hashref) {
                my $name = $ref->{'name'};