From 52b32bda02023473ee5b411e132ed54eb1f3a86b Mon Sep 17 00:00:00 2001
From: Jordan Doyle <jordan@doyle.la>
Date: Mon, 23 May 2022 03:31:59 +0100
Subject: [PATCH] Significantly optimise fetch_latest_blocks

---
 web-api/src/database/blocks.rs | 62 ++++++++++++++++++++++++++++++++++----------------------------
 1 file changed, 34 insertions(+), 28 deletions(-)

diff --git a/web-api/src/database/blocks.rs b/web-api/src/database/blocks.rs
index 1ff9f51..0bb608b 100644
--- a/web-api/src/database/blocks.rs
+++ b/web-api/src/database/blocks.rs
@@ -49,29 +49,34 @@ pub async fn fetch_latest_blocks(
     count: i64,
     offset: i64,
 ) -> Result<Vec<(Block, TransactionCount, TransactionWeight, Vec<u8>)>> {
+    let query = "
+        SELECT
+            blocks.*,
+            tx.count,
+            tx.weight,
+            (
+                SELECT script
+                FROM transactions
+                INNER JOIN transaction_inputs
+                ON transaction_inputs.transaction_id = transactions.id
+                WHERE transactions.block_id = blocks.id
+                AND transactions.coinbase = true
+                LIMIT 1
+            ) AS coinbase_script
+        FROM blocks
+        LEFT JOIN LATERAL (
+            SELECT
+                SUM(transactions.weight) AS weight,
+                COUNT(transactions.id) AS count
+            FROM transactions
+            WHERE transactions.block_id = blocks.id
+        ) tx ON true
+        ORDER BY blocks.height
+        LIMIT $1 OFFSET $2
+    ";
+
     let blocks = db
-        .query(
-            "SELECT
-               blocks.*,
-               COUNT(transactions.id) AS tx_count,
-               SUM(transactions.weight) AS tx_weight,
-               (
-                 SELECT script
-                 FROM transactions
-                 INNER JOIN transaction_inputs
-                   ON transaction_inputs.transaction_id = transactions.id
-                 WHERE transactions.block_id = blocks.id
-                   AND transactions.coinbase = true
-                 LIMIT 1
-               ) AS coinbase_script
-             FROM blocks
-             LEFT JOIN transactions
-               ON transactions.block_id = blocks.id
-             GROUP BY blocks.id
-             ORDER BY blocks.height DESC
-             LIMIT $1 OFFSET $2",
-            &[&count, &offset],
-        )
+        .query(query, &[&count, &offset])
         .await?;
 
     blocks
@@ -86,13 +91,14 @@ pub async fn fetch_latest_blocks(
 }
 
 pub async fn fetch_block_by_height(db: &Connection, height: i64) -> Result<Option<Block>> {
+    let query = "
+        SELECT *
+        FROM blocks
+        WHERE height = $1
+    ";
+
     let block = db
-        .query_opt(
-            "SELECT *
-         FROM blocks
-         WHERE height = $1",
-            &[&height],
-        )
+        .query_opt(query, &[&height])
         .await?;
 
     Ok(block.map(Block::from_row).transpose()?)
--
libgit2 1.7.2