From 83e4930b7e5e001d0fe245cb432879238e6ccd92 Mon Sep 17 00:00:00 2001
From: David Kaufmann <astra@ionic.at>
Date: Sun, 7 Apr 2019 23:04:16 +0200
Subject: [PATCH] formatting

---
 ex1/main_5.tex | 33 ++++++++++++++++++++++++---------
 1 file changed, 24 insertions(+), 9 deletions(-)

diff --git a/ex1/main_5.tex b/ex1/main_5.tex
index 3a3531d..4dfdeda 100644
--- a/ex1/main_5.tex
+++ b/ex1/main_5.tex
@@ -2,10 +2,12 @@
 
 \begin{enumerate}[label=(\alph*)]
 % (a)
-	\item
+\item
 Optimized query:
 \begin{verbatim}
-SELECT distinct(displayname) FROM posts p JOIN users u ON u.id = p.owneruserid WHERE p.viewcount > u.views;
+SELECT distinct(displayname) FROM posts p
+    JOIN users u ON u.id = p.owneruserid
+    WHERE p.viewcount > u.views;
 \end{verbatim}
 
 Improvements:\\
@@ -24,10 +26,10 @@ Optimized version:
 \end{verbatim}
 
 % (b)
-	\item
+\item
 Optimized query:
 \begin{verbatim}
-SELECT score FROM comments WHERE text ILIKE 'yes\%'; <- did not work (100ms instead of 33ms)
+SELECT score FROM comments WHERE text ILIKE 'yes\%';
 \end{verbatim}
 
 Original version:
@@ -42,10 +44,12 @@ TODO, current query is slower..
 \end{verbatim}
 
 % (b)
-	\item
+\item
 Optimized query:
 \begin{verbatim}
-SELECT DISTINCT postid FROM votes WHERE postid NOT IN (SELECT postid FROM votes WHERE votetypeid != 2);
+SELECT DISTINCT postid FROM votes WHERE postid NOT IN (
+    SELECT postid FROM votes WHERE votetypeid != 2
+);
 \end{verbatim}
 
 Improvements:\\
@@ -53,7 +57,8 @@ Instead of doing about twelve million Seq Scans this is now reduces it to two Se
 
 Original version:
 \begin{verbatim}
-Did not terminate. On a different machine with SSD and no time limit it took 16 minutes.
+Did not terminate.
+On a different machine with SSD and no time limit it took 16 minutes.
 \end{verbatim}
 
 Optimized version:
@@ -63,10 +68,20 @@ Optimized version:
 \end{verbatim}
 
 % (d)
-	\item
+\item
 Optimized query:
 \begin{verbatim}
-SELECT p.*, c.*, u.* FROM posts p JOIN comments c ON p.id = c.postid JOIN users u ON p.owneruserid = u.id JOIN badges b on u.id = b.userid WHERE u.upvotes+3 >= (SELECT AVG(upvotes) FROM users WHERE u.creationdate > c.creationdate) AND EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid > p.id) AND b.name IN ('Autobiographer','Supporter');
+SELECT p.*, c.*, u.* FROM posts p
+    JOIN comments c ON p.id = c.postid
+    JOIN users u ON p.owneruserid = u.id
+    JOIN badges b on u.id = b.userid
+    WHERE u.upvotes+3 >= (
+        SELECT AVG(upvotes) FROM users
+            WHERE u.creationdate > c.creationdate
+        ) AND EXISTS (
+            SELECT 1 FROM postlinks l
+                WHERE l.relatedpostid > p.id
+        ) AND b.name IN ('Autobiographer','Supporter');
 \end{verbatim}
 
 Original version:
-- 
2.43.0