ex1/main_4.tex

   1 %ex1.4
   2
   3 \begin{enumerate}[label=(\alph*)]
   4 \item Hash Join in Merge Join.\\
   5 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT a,b,c FROM r NATURAL JOIN s NATURAL JOIN t;\\
   6 \begin{verbatim}
   7 Merge Join (cost=126688.84..189746.85 rows=4812309 width=12)
   8       (actual time=1223.252..3564.720 rows=4790517 loops=1)
   9   Output: r.a, r.b, s.c
  10   Merge Cond: ((t.a = r.a) AND (t.c = s.c))
  11   Buffers: shared hit=138, temp read=1320 written=3593
  12   -> Sort (cost=809.39..834.39 rows=10000 width=8)
  13      (actual time=6.542..9.442 rows=10000 loops=1)
  14     Output: t.a, t.c
  15     Sort Key: t.a, t.c
  16     Sort Method: quicksort Memory: 853kB
  17     Buffers: shared hit=45
  18     -> Seq Scan on public.t (cost=0.00..145.00 rows=10000 width=8)
  19                      (actual time=0.013..1.698 rows=10000 loops=1)
  20         Output: t.a, t.c
  21         Buffers: shared hit=45
  22   -> Materialize (cost=125878.37..130770.59 rows=978445 width=12)
  23             (actual time=1216.698..1848.300 rows=4790518 loops=1)
  24     Output: r.a, r.b, s.c
  25     Buffers: shared hit=93, temp read=1320 written=3593
  26     -> Sort (cost=125878.37..128324.48 rows=978445 width=12)
  27        (actual time=1216.694..1265.485 rows=105629 loops=1)
  28         Output: r.a, r.b, s.c
  29         Sort Key: r.a, s.c
  30         Sort Method: external merge Disk: 21312kB
  31         Buffers: shared hit=93, temp read=1320 written=3593
  32         -> Hash Join (cost=270.00..11799.45 rows=978445 width=12)
  33                 (actual time=5.707..326.759 rows=993774 loops=1)
  34           Output: r.a, r.b, s.c
  35           Hash Cond: (r.b = s.b)
  36           Buffers: shared hit=93
  37           -> Seq Scan on public.r (cost=0.00..145.00 rows=10000 width=8)
  38                            (actual time=0.013..2.200 rows=10000 loops=1)
  39               Output: r.a, r.b
  40               Buffers: shared hit=45
  41           -> Hash  (cost=145.00..145.00 rows=10000 width=8)
  42               (actual time=5.636..5.636 rows=10000 loops=1)
  43               Output: s.c, s.b
  44               Buckets: 16384  Batches: 1 Memory Usage: 519kB
  45               Buffers: shared hit=45
  46               -> Seq Scan on public.s (cost=0.00..145.00 rows=10000 width=8)
  47                                (actual time=0.012..2.656 rows=10000 loops=1)
  48                 Output: s.c, s.b
  49                 Buffers: shared hit=45
  50 Planning time: 0.448 ms
  51 Execution time: 4093.957 ms
  52 \end{verbatim}
  53
  54
  55 \item
  56 set enable\_hashjoin=1; set enable\_mergejoin=0; set enable\_nestloop=0;
  57 \begin{verbatim}
  58 Hash Join (cost=565.00..382720.42 rows=4812309 width=12)
  59     (actual time=10.065..2336.284 rows=4790517 loops=1)
  60 Execution time: 2839.835 ms
  61 \end{verbatim}
  62
  63
  64 set enable\_hashjoin=0; set enable\_mergejoin=1; set enable\_nestloop=0;
  65 \begin{verbatim}
  66 Merge Join (cost=127309.80..190367.82 rows=4812309 width=12)
  67       (actual time=1400.115..3961.542 rows=4790517 loops=1)
  68 Planning time: 0.374 ms
  69 Execution time: 4215.626 ms
  70 \end{verbatim}
  71
  72 set enable\_hashjoin=0; set enable\_mergejoin=0; set enable\_nestloop=1;
  73 \begin{verbatim}
  74 Nested Loop (cost=0.00..172728360.00 rows=4812309 width=12)
  75 Execution time: query-timeout
  76 \end{verbatim}
  77
  78 TODO: Explain perfomance diffrences.
  79
  80 TODO: change create numbers to change performance. -> (non)duplicate stuff?
  81
  82 \item create indices.
  83 \begin{verbatim}
  84 create index i1 on r(a);
  85 create index i2 on r(b);
  86 create index i3 on s(b);
  87 create index i4 on s(c);
  88 create index i5 on t(a);
  89 create index i6 on t(c);
  90
  91 create index ii1 on r(a,b);
  92 create index ii2 on s(b,c);
  93 create index ii3 on t(a,c);
  94
  95 REINDEX DATABASE u00726236;
  96 \end{verbatim}
  97
  98 \newpage
  99 New query plan for merge join.
 100 \begin{verbatim}
 101 Merge Join (cost=126115.03..189598.35 rows=4812309 width=12)
 102       (actual time=1471.775..3798.577 rows=4790517 loops=1)
 103   Output: r.a, r.b, s.c
 104   Merge Cond: ((t.a = r.a) AND (t.c = s.c))
 105   Buffers: shared hit=7289 read=58, temp read=1325 written=3598
 106   ->  Index Only Scan using ii3 on public.t (cost=0.29..449.50 rows=10000 width=8)
 107                                      (actual time=0.034..8.250 rows=10000 loops=1)
 108     Output: t.a, t.c
 109     Heap Fetches: 10000
 110     Buffers: shared hit=6299 read=29
 111   ->  Materialize (cost=126114.75..131006.97 rows=978445 width=12)
 112              (actual time=1471.732..2093.037 rows=4790518 loops=1)
 113     Output: r.a, r.b, s.c
 114     Buffers: shared hit=990 read=29, temp read=1325 written=3598
 115     ->  Sort (cost=126114.75..128560.86 rows=978445 width=12)
 116         (actual time=1471.727..1519.766 rows=105629 loops=1)
 117         Output: r.a, r.b, s.c
 118         Sort Key: r.a, s.c
 119         Sort Method: external merge  Disk: 21312kB
 120         Buffers: shared hit=990 read=29, temp read=1325 written=3598
 121         ->  Merge Join (cost=809.67..12035.83 rows=978445 width=12)
 122                   (actual time=5.865..568.309 rows=993774 loops=1)
 123           Output: r.a, r.b, s.c
 124           Merge Cond: (r.b = s.b)
 125           Buffers: shared hit=990 read=29
 126           ->  Index Scan using i2 on public.r (cost=0.29..449.80 rows=10000 width=8)
 127                                        (actual time=0.026..7.013 rows=10000 loops=1)
 128               Output: r.a, r.b
 129               Buffers: shared hit=945 read=29
 130           ->  Sort (cost=809.39..834.39 rows=10000 width=8)
 131             (actual time=5.836..189.024 rows=993775 loops=1)
 132               Output: s.c, s.b
 133               Sort Key: s.b
 134               Sort Method: quicksort Memory: 853kB
 135               Buffers: shared hit=45
 136               ->  Seq Scan on public.s (cost=0.00..145.00 rows=10000 width=8)
 137                                 (actual time=0.013..2.654 rows=10000 loops=1)
 138                 Output: s.c, s.b
 139                 Buffers: shared hit=45
 140 Planning time: 1.060 ms
 141 Execution time: 4323.022 ms
 142 \end{verbatim}
 143
 144 It seems like no performance improvement is made: Query planning and execution time is bigger with indices.
 145
 146
 147 New query plan for merge join.
 148 \begin{verbatim}
 149 Nested Loop (cost=0.57..498778.26 rows=4812309 width=12)
 150      (actual time=0.096..8351.002 rows=4790517 loops=1)
 151   Output: r.a, r.b, s.c
 152   Buffers: shared hit=7476815 read=11
 153   ->  Nested Loop (cost=0.29..30520.00 rows=1007007 width=12)
 154           (actual time=0.076..1017.339 rows=987869 loops=1)
 155         Output: r.a, r.b, t.c
 156         Buffers: shared hit=950972 read=4
 157         ->  Seq Scan on public.t (cost=0.00..145.00 rows=10000 width=8)
 158                           (actual time=0.015..2.720 rows=10000 loops=1)
 159               Output: t.a, t.c
 160               Buffers: shared hit=45
 161         ->  Index Only Scan using ii1 on public.r (cost=0.29..2.05 rows=99 width=8)
 162                                          (actual time=0.007..0.076 rows=99 loops=10000)
 163               Output: r.a, r.b
 164               Index Cond: (r.a = t.a)
 165               Heap Fetches: 987869
 166               Buffers: shared hit=950927 read=4
 167   ->  Index Only Scan using ii2 on public.s (cost=0.29..0.41 rows=5 width=8)
 168                                    (actual time=0.003..0.006 rows=5 loops=987869)
 169         Output: s.b, s.c
 170         Index Cond: ((s.b = r.b) AND (s.c = t.c))
 171         Heap Fetches: 4790517
 172         Buffers: shared hit=6525843 read=7
 173 Planning time: 0.604 ms
 174 Execution time: 8884.429 ms
 175 \end{verbatim}
 176 Suddenly nested loop joins become feasible.
 177
 178 \end{enumerate}