From 14a7f75fdfb2f83852430e9c985bd35aa0cb5a6c Mon Sep 17 00:00:00 2001
From: Jan Vales <jan@jvales.net>
Date: Mon, 8 Apr 2019 01:41:44 +0200
Subject: [PATCH] reformat ex4.4

---
 ex1/main_4.tex | 70 ++++++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 59 insertions(+), 11 deletions(-)

diff --git a/ex1/main_4.tex b/ex1/main_4.tex
index a8bb3bd..cd9ae25 100644
--- a/ex1/main_4.tex
+++ b/ex1/main_4.tex
@@ -1,8 +1,13 @@
 %ex1.4
 
 \begin{enumerate}[label=(\alph*)]
-\item Hash Join in Merge Join.\\
-EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;\\
+\item Hash Join in Merge Join.
+\begin{verbatim}
+EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;
+\end{verbatim}
+
+Query plan
 \begin{verbatim}
 Merge Join (cost=126688.84..189746.85 rows=4812309 width=12)
       (actual time=1223.252..3564.720 rows=4790517 loops=1)
@@ -52,8 +57,15 @@ Execution time: 4093.957 ms
 \end{verbatim}
 
 
+
 \item
+\begin{verbatim}
 set enable\_hashjoin=1; set enable\_mergejoin=0; set enable\_nestloop=0;
+EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;
+\end{verbatim}
+
+Results
 \begin{verbatim}
 Hash Join (cost=565.00..382720.42 rows=4812309 width=12)
     (actual time=10.065..2336.284 rows=4790517 loops=1)
@@ -61,7 +73,13 @@ Execution time: 2839.835 ms
 \end{verbatim}
 
 
+\begin{verbatim}
 set enable\_hashjoin=0; set enable\_mergejoin=1; set enable\_nestloop=0;
+EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;
+\end{verbatim}
+
+Results
 \begin{verbatim}
 Merge Join (cost=127309.80..190367.82 rows=4812309 width=12)
       (actual time=1400.115..3961.542 rows=4790517 loops=1)
@@ -69,16 +87,26 @@ Planning time: 0.374 ms
 Execution time: 4215.626 ms
 \end{verbatim}
 
+
+\begin{verbatim}
 set enable\_hashjoin=0; set enable\_mergejoin=0; set enable\_nestloop=1;
+EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;
+\end{verbatim}
+
+Results
 \begin{verbatim}
 Nested Loop (cost=0.00..172728360.00 rows=4812309 width=12)
 Execution time: query-timeout
 \end{verbatim}
 
+
 TODO: Explain perfomance diffrences.
 
 TODO: change create numbers to change performance. -> (non)duplicate stuff?
 
+
+
 \item create indices.
 \begin{verbatim}
 create index i1 on r(a);
@@ -87,15 +115,16 @@ create index i3 on s(b);
 create index i4 on s(c);
 create index i5 on t(a);
 create index i6 on t(c);
-
 create index ii1 on r(a,b);
 create index ii2 on s(b,c);
 create index ii3 on t(a,c);
+REINDEX DATABASE u00726236; VACUUM ANALYZE;
 
-REINDEX DATABASE u00726236;
+RESET ALL;
+EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;
 \end{verbatim}
 
-\newpage
 New query plan for merge join.
 \begin{verbatim}
 Merge Join (cost=126115.03..189598.35 rows=4812309 width=12)
@@ -144,7 +173,7 @@ Execution time: 4323.022 ms
 It seems like no performance improvement is made: Query planning and execution time is bigger with indices.
 
 
-New query plan for merge join.
+Suddenly nested loop joins become feasible.
 \begin{verbatim}
 Nested Loop (cost=0.57..498778.26 rows=4812309 width=12)
      (actual time=0.096..8351.002 rows=4790517 loops=1)
@@ -173,10 +202,17 @@ Nested Loop (cost=0.57..498778.26 rows=4812309 width=12)
 Planning time: 0.604 ms
 Execution time: 8884.429 ms
 \end{verbatim}
-Suddenly nested loop joins become feasible.
 
 
-\item RESET ALL; EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT a,b,c FROM r NATURAL JOIN t WHERE b IN (SELECT s.b FROM s);\\
+
+
+\newpage
+\begin{verbatim}
+RESET ALL; EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM r NATURAL JOIN t WHERE b IN (SELECT s.b FROM s);
+\end{verbatim}
+
+Query plan
 \begin{verbatim}
 Hash Join (cost=578.59..12368.66 rows=1007007 width=12)
     (actual time=25.336..340.303 rows=987869 loops=1)
@@ -223,7 +259,7 @@ It now takes only 439.7 ms to execute the query, instead of the previous 4323 ms
 
 
 \item This was the case with (d) already.\\
-But if we create indices we get a merge join + hash semi join combination, which is a little bit slower.
+But if we create indexes we get a merge join + hash semi join combination, which is a little bit slower.
 \begin{verbatim}
 Merge Join (cost=1118.26..12199.68 rows=1007007 width=12)
       (actual time=23.151..517.241 rows=987869 loops=1)
@@ -268,7 +304,13 @@ Execution time: 624.356 ms
 \end{verbatim}
 
 
-\item RESET ALL; EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT a,b,c FROM r NATURAL JOIN s WHERE a IN (SELECT t.a FROM t);\\
+
+\item
+\begin{verbatim}
+RESET ALL; EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM r NATURAL JOIN s WHERE a IN (SELECT t.a FROM t);
+\end{verbatim}
+
 Without index: hash join + hash semi join
 \begin{verbatim}
 Hash Join (cost=440.25..1943.04 rows=112490 width=12)
@@ -286,7 +328,12 @@ Execution time: 65.131 ms
 \end{verbatim}
 
 
-RESET ALL; EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT a,b,c FROM s NATURAL JOIN t WHERE a IN (SELECT r.a FROM r);
+
+\begin{verbatim}
+RESET ALL; EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
+SELECT a,b,c FROM s NATURAL JOIN t WHERE a IN (SELECT r.a FROM r);
+\end{verbatim}
+
 Without index: hash join + hash semi join
 \begin{verbatim}
 Hash Join  (cost=442.27..56192.50 rows=4884102 width=12)
@@ -303,6 +350,7 @@ Planning time: 0.978 ms
 Execution time: 2040.407 ms
 \end{verbatim}
 
+
 TODO: Discuss why.\\
 -> Indizes quasi irrelevant.\\
 -> performance unterschiede weil unterschiedliche set mächtigkeiten?
-- 
2.43.0