From 4566f404a8b53c61620380fb910941f482d44526 Mon Sep 17 00:00:00 2001
From: David Kaufmann <astra@ionic.at>
Date: Mon, 8 Apr 2019 03:24:13 +0200
Subject: [PATCH] improve ex1.5.d

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

diff --git a/ex1/main_5.tex b/ex1/main_5.tex
index c7273be..4779678 100644
--- a/ex1/main_5.tex
+++ b/ex1/main_5.tex
@@ -122,6 +122,9 @@ Planning time: 4.560 ms
 Execution time: 4406.299 ms
 \end{verbatim}
 
+Improvements:
+
+Converted the joins via WHERE clause to proper JOINs
 
 Attempt at optimization
 \begin{verbatim}
@@ -167,4 +170,25 @@ Planning time: 3.295 ms
 Execution time: 3330.766 ms
 \end{verbatim}
 
+And move that semijoin for each post to a simple value comparison
+\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
+b.name IN ('Autobiographer','Supporter')
+AND p.id < (SELECT MAX(relatedpostid) FROM postlinks)
+AND u.upvotes+3 >=
+    (SELECT AVG(upvotes) FROM users WHERE u.creationdate > c.creationdate);
+\end{verbatim}
+
+Results
+\begin{verbatim}
+Hash Join  (cost=4274.13..3221469.71 rows=28448 width=1506)
+    (actual time=261.341..369.495 rows=10 loops=1)
+Planning time: 4.422 ms
+Execution time: 370.932 ms
+\end{verbatim}
+
 \end{enumerate}
-- 
2.43.0