From 981401fb095f304020b6cf3f84f44b78b09e99a0 Mon Sep 17 00:00:00 2001 From: Jan Vales Date: Sat, 6 Apr 2019 00:49:07 +0200 Subject: [PATCH] Ex1.4 a-c --- ex1/main_4.tex | 195 ++++++++++++++++++++++++++++++++++++++++--------- 1 file changed, 161 insertions(+), 34 deletions(-) diff --git a/ex1/main_4.tex b/ex1/main_4.tex index f674397..e4c3c23 100644 --- a/ex1/main_4.tex +++ b/ex1/main_4.tex @@ -1,51 +1,178 @@ %ex1.4 \begin{enumerate}[label=(\alph*)] -\item Hash Join, then Merge Join +\item Hash Join in Merge Join.\\ +EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;\\ \begin{verbatim} -Merge Join (cost=126688.84..189746.85 rows=4812309 width=12) (actual time=1235.201..3507.532 rows=4790517 loops=1) +Merge Join (cost=126688.84..189746.85 rows=4812309 width=12) + (actual time=1223.252..3564.720 rows=4790517 loops=1) Output: r.a, r.b, s.c Merge Cond: ((t.a = r.a) AND (t.c = s.c)) Buffers: shared hit=138, temp read=1320 written=3593 - -> Sort (cost=809.39..834.39 rows=10000 width=8) (actual time=15.180..17.809 rows=10000 loops=1) + -> Sort (cost=809.39..834.39 rows=10000 width=8) + (actual time=6.542..9.442 rows=10000 loops=1) + Output: t.a, t.c + Sort Key: t.a, t.c + Sort Method: quicksort Memory: 853kB + Buffers: shared hit=45 + -> Seq Scan on public.t (cost=0.00..145.00 rows=10000 width=8) + (actual time=0.013..1.698 rows=10000 loops=1) Output: t.a, t.c - Sort Key: t.a, t.c - Sort Method: quicksort Memory: 853kB Buffers: shared hit=45 - -> Seq Scan on public.t (cost=0.00..145.00 rows=10000 width=8) (actual time=0.036..4.271 rows=10000 loops=1) - Output: t.a, t.c - Buffers: shared hit=45 - -> Materialize (cost=125878.37..130770.59 rows=978445 width=12) (actual time=1220.009..1810.482 rows=4790518 loops=1) + -> Materialize (cost=125878.37..130770.59 rows=978445 width=12) + (actual time=1216.698..1848.300 rows=4790518 loops=1) + Output: r.a, r.b, s.c + Buffers: shared hit=93, temp read=1320 written=3593 + -> Sort (cost=125878.37..128324.48 rows=978445 width=12) + (actual time=1216.694..1265.485 rows=105629 loops=1) Output: r.a, r.b, s.c + Sort Key: r.a, s.c + Sort Method: external merge Disk: 21312kB Buffers: shared hit=93, temp read=1320 written=3593 - -> Sort (cost=125878.37..128324.48 rows=978445 width=12) (actual time=1220.003..1267.097 rows=105629 loops=1) - Output: r.a, r.b, s.c - Sort Key: r.a, s.c - Sort Method: external merge Disk: 21312kB - Buffers: shared hit=93, temp read=1320 written=3593 - -> Hash Join (cost=270.00..11799.45 rows=978445 width=12) (actual time=7.133..326.364 rows=993774 loops=1) - Output: r.a, r.b, s.c - Hash Cond: (r.b = s.b) - Buffers: shared hit=93 - -> Seq Scan on public.r (cost=0.00..145.00 rows=10000 width=8) (actual time=0.023..2.157 rows=10000 loops=1) - Output: r.a, r.b - Buffers: shared hit=45 - -> Hash (cost=145.00..145.00 rows=10000 width=8) (actual time=7.022..7.022 rows=10000 loops=1) - Output: s.c, s.b - Buckets: 16384 Batches: 1 Memory Usage: 519kB - Buffers: shared hit=45 - -> Seq Scan on public.s (cost=0.00..145.00 rows=10000 width=8) (actual time=0.021..3.459 rows=10000 loops=1) - Output: s.c, s.b - Buffers: shared hit=45 -Planning time: 0.899 ms -Execution time: 4040.426 ms - + -> Hash Join (cost=270.00..11799.45 rows=978445 width=12) + (actual time=5.707..326.759 rows=993774 loops=1) + Output: r.a, r.b, s.c + Hash Cond: (r.b = s.b) + Buffers: shared hit=93 + -> Seq Scan on public.r (cost=0.00..145.00 rows=10000 width=8) + (actual time=0.013..2.200 rows=10000 loops=1) + Output: r.a, r.b + Buffers: shared hit=45 + -> Hash (cost=145.00..145.00 rows=10000 width=8) + (actual time=5.636..5.636 rows=10000 loops=1) + Output: s.c, s.b + Buckets: 16384 Batches: 1 Memory Usage: 519kB + Buffers: shared hit=45 + -> Seq Scan on public.s (cost=0.00..145.00 rows=10000 width=8) + (actual time=0.012..2.656 rows=10000 loops=1) + Output: s.c, s.b + Buffers: shared hit=45 +Planning time: 0.448 ms +Execution time: 4093.957 ms \end{verbatim} + \item +set enable\_hashjoin=1; set enable\_mergejoin=0; set enable\_nestloop=0; +\begin{verbatim} +Hash Join (cost=565.00..382720.42 rows=4812309 width=12) + (actual time=10.065..2336.284 rows=4790517 loops=1) +Execution time: 2839.835 ms +\end{verbatim} + + +set enable\_hashjoin=0; set enable\_mergejoin=1; set enable\_nestloop=0; +\begin{verbatim} +Merge Join (cost=127309.80..190367.82 rows=4812309 width=12) + (actual time=1400.115..3961.542 rows=4790517 loops=1) +Planning time: 0.374 ms +Execution time: 4215.626 ms +\end{verbatim} + +set enable\_hashjoin=0; set enable\_mergejoin=0; set enable\_nestloop=1; +\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); +create index i2 on r(b); +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; +\end{verbatim} + +\newpage +New query plan for merge join. +\begin{verbatim} +Merge Join (cost=126115.03..189598.35 rows=4812309 width=12) + (actual time=1471.775..3798.577 rows=4790517 loops=1) + Output: r.a, r.b, s.c + Merge Cond: ((t.a = r.a) AND (t.c = s.c)) + Buffers: shared hit=7289 read=58, temp read=1325 written=3598 + -> Index Only Scan using ii3 on public.t (cost=0.29..449.50 rows=10000 width=8) + (actual time=0.034..8.250 rows=10000 loops=1) + Output: t.a, t.c + Heap Fetches: 10000 + Buffers: shared hit=6299 read=29 + -> Materialize (cost=126114.75..131006.97 rows=978445 width=12) + (actual time=1471.732..2093.037 rows=4790518 loops=1) + Output: r.a, r.b, s.c + Buffers: shared hit=990 read=29, temp read=1325 written=3598 + -> Sort (cost=126114.75..128560.86 rows=978445 width=12) + (actual time=1471.727..1519.766 rows=105629 loops=1) + Output: r.a, r.b, s.c + Sort Key: r.a, s.c + Sort Method: external merge Disk: 21312kB + Buffers: shared hit=990 read=29, temp read=1325 written=3598 + -> Merge Join (cost=809.67..12035.83 rows=978445 width=12) + (actual time=5.865..568.309 rows=993774 loops=1) + Output: r.a, r.b, s.c + Merge Cond: (r.b = s.b) + Buffers: shared hit=990 read=29 + -> Index Scan using i2 on public.r (cost=0.29..449.80 rows=10000 width=8) + (actual time=0.026..7.013 rows=10000 loops=1) + Output: r.a, r.b + Buffers: shared hit=945 read=29 + -> Sort (cost=809.39..834.39 rows=10000 width=8) + (actual time=5.836..189.024 rows=993775 loops=1) + Output: s.c, s.b + Sort Key: s.b + Sort Method: quicksort Memory: 853kB + Buffers: shared hit=45 + -> Seq Scan on public.s (cost=0.00..145.00 rows=10000 width=8) + (actual time=0.013..2.654 rows=10000 loops=1) + Output: s.c, s.b + Buffers: shared hit=45 +Planning time: 1.060 ms +Execution time: 4323.022 ms +\end{verbatim} + +It seems like no performance improvement is made: Query planning and execution time is bigger with indices. -Only Merge join: Total cost: 190481.63 -Only Hash join: Total cost: 378418.91 -Only Nestloop: Total cost: 174352535.00 + +New query plan for merge join. +\begin{verbatim} +Nested Loop (cost=0.57..498778.26 rows=4812309 width=12) + (actual time=0.096..8351.002 rows=4790517 loops=1) + Output: r.a, r.b, s.c + Buffers: shared hit=7476815 read=11 + -> Nested Loop (cost=0.29..30520.00 rows=1007007 width=12) + (actual time=0.076..1017.339 rows=987869 loops=1) + Output: r.a, r.b, t.c + Buffers: shared hit=950972 read=4 + -> Seq Scan on public.t (cost=0.00..145.00 rows=10000 width=8) + (actual time=0.015..2.720 rows=10000 loops=1) + Output: t.a, t.c + Buffers: shared hit=45 + -> Index Only Scan using ii1 on public.r (cost=0.29..2.05 rows=99 width=8) + (actual time=0.007..0.076 rows=99 loops=10000) + Output: r.a, r.b + Index Cond: (r.a = t.a) + Heap Fetches: 987869 + Buffers: shared hit=950927 read=4 + -> Index Only Scan using ii2 on public.s (cost=0.29..0.41 rows=5 width=8) + (actual time=0.003..0.006 rows=5 loops=987869) + Output: s.b, s.c + Index Cond: ((s.b = r.b) AND (s.c = t.c)) + Heap Fetches: 4790517 + Buffers: shared hit=6525843 read=7 +Planning time: 0.604 ms +Execution time: 8884.429 ms +\end{verbatim} +Suddenly nested loop joins become feasible. \end{enumerate} -- 2.43.0