From ba1d6ed9656b5c5b1191a205855eda77197e087a Mon Sep 17 00:00:00 2001
From: David Kaufmann <astra@ionic.at>
Date: Sun, 7 Apr 2019 22:42:02 +0200
Subject: [PATCH] add queries and timings
---
ex1/main_5.tex | 77 ++++++++++++++++++++++++++++++++++++++++++++++----
1 file changed, 72 insertions(+), 5 deletions(-)
diff --git a/ex1/main_5.tex b/ex1/main_5.tex
index 8c13d5f..c800914 100644
--- a/ex1/main_5.tex
+++ b/ex1/main_5.tex
@@ -1,13 +1,80 @@
%ex1.5
\begin{enumerate}[label=(\alph*)]
- \item SELECT distinct(displayname) FROM posts p JOIN users u ON u.id = p.owneruserid WHERE p.viewcount > u.views;
+% (a)
+ \item
+Optimized query:
+\begin{verbatim}
+SELECT distinct(displayname) FROM posts p JOIN users u ON u.id = p.owneruserid WHERE p.viewcount > u.views;
+\end{verbatim}
-local: 45s vs 31ms
+Original version:
+\begin{verbatim}
+ Planning time: 0.978 ms
+ Execution time: 86179.847 ms
+\end{verbatim}
- \item SELECT score FROM comments WHERE text ILIKE 'yes\%'; <- did not work (100ms instead of 33ms)
+Optimized version:
+\begin{verbatim}
+ Planning time: 0.960 ms
+ Execution time: 37.354 ms
+\end{verbatim}
- \item SELECT DISTINCT postid FROM votes WHERE postid NOT IN (SELECT postid FROM votes WHERE votetypeid != 2);
+% (b)
+ \item
+Optimized query:
+\begin{verbatim}
+SELECT score FROM comments WHERE text ILIKE 'yes\%'; <- did not work (100ms instead of 33ms)
+\end{verbatim}
-local: 16m vs 35ms
+Original version:
+\begin{verbatim}
+ Planning time: 0.540 ms
+ Execution time: 60.946 ms
+\end{verbatim}
+
+Optimized version:
+\begin{verbatim}
+TODO, current query is slower..
+\end{verbatim}
+
+% (b)
+ \item
+Optimized query:
+\begin{verbatim}
+SELECT DISTINCT postid FROM votes WHERE postid NOT IN (SELECT postid FROM votes WHERE votetypeid != 2);
+\end{verbatim}
+
+Improvements:\\
+Instead of doing about twelve million Seq Scans this is now reduces it to two Seq Scans by inverting the condition.
+
+Original version:
+\begin{verbatim}
+Did not terminate. On a different machine with SSD and no time limit it took 16 minutes.
+\end{verbatim}
+
+Optimized version:
+\begin{verbatim}
+ Planning time: 1.093 ms
+ Execution time: 81.776 ms
+\end{verbatim}
+
+% (d)
+ \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');
+\end{verbatim}
+
+Original version:
+\begin{verbatim}
+ Planning time: 4.084 ms
+ Execution time: 4006.044 ms
+\end{verbatim}
+
+Optimized version:
+\begin{verbatim}
+ Planning time: 2.954 ms
+ Execution time: 3081.461 ms
+\end{verbatim}
\end{enumerate}
--
2.43.0