From 3fab58827017041168a769184469cff3722d6c38 Mon Sep 17 00:00:00 2001
From: Owen Jacobson <owen@grimoire.ca>
Date: Wed, 23 Oct 2024 00:31:53 -0400
Subject: Make sure (most) queries avoid table scans.

I've exempted inserts (they never scan in the first place), queries on `event_sequence` (at most one row), and the coalesce()s used for event replay (for now; these are obviously a performance risk area and need addressing).

Method:

```
find .sqlx -name 'query-*.json' -exec jq -r '"explain query plan " + .query + ";"' {} + > explain.sql
```

Then go query by query through the resulting file.
---
 src/channel/repo.rs | 14 +++++++-------
 1 file changed, 7 insertions(+), 7 deletions(-)

(limited to 'src/channel')

diff --git a/src/channel/repo.rs b/src/channel/repo.rs
index e26ac2b..a49db52 100644
--- a/src/channel/repo.rs
+++ b/src/channel/repo.rs
@@ -104,13 +104,13 @@ impl<'c> Channels<'c> {
         Ok(channel)
     }
 
-    pub async fn all(&mut self, resume_at: ResumePoint) -> Result<Vec<History>, LoadError> {
+    pub async fn all(&mut self, resume_at: Sequence) -> Result<Vec<History>, LoadError> {
         let channels = sqlx::query!(
             r#"
                 select
                     id as "id: Id",
-                    name.display_name as "display_name: String",
-                    name.canonical_name as "canonical_name: String",
+                    name.display_name as "display_name?: String",
+                    name.canonical_name as "canonical_name?: String",
                     channel.created_at as "created_at: DateTime",
                     channel.created_sequence as "created_sequence: Sequence",
                     deleted.deleted_at as "deleted_at?: DateTime",
@@ -120,7 +120,7 @@ impl<'c> Channels<'c> {
                     using (id)
                 left join channel_deleted as deleted
                     using (id)
-                where coalesce(channel.created_sequence <= $1, true)
+                where channel.created_sequence <= $1
                 order by name.canonical_name
             "#,
             resume_at,
@@ -144,7 +144,7 @@ impl<'c> Channels<'c> {
         Ok(channels)
     }
 
-    pub async fn replay(&mut self, resume_at: Option<Sequence>) -> Result<Vec<History>, LoadError> {
+    pub async fn replay(&mut self, resume_at: ResumePoint) -> Result<Vec<History>, LoadError> {
         let channels = sqlx::query!(
             r#"
                 select
@@ -263,8 +263,8 @@ impl<'c> Channels<'c> {
             r#"
                 select
                     channel.id as "id: Id",
-                    name.display_name as "display_name: String",
-                    name.canonical_name as "canonical_name: String",
+                    name.display_name as "display_name?: String",
+                    name.canonical_name as "canonical_name?: String",
                     channel.created_at as "created_at: DateTime",
                     channel.created_sequence as "created_sequence: Sequence",
                     deleted.deleted_at as "deleted_at?: DateTime",
-- 
cgit v1.2.3