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
 179 \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);\\
 180 \begin{verbatim}
 181 Hash Join (cost=578.59..12368.66 rows=1007007 width=12)
 182     (actual time=25.336..340.303 rows=987869 loops=1)
 183   Output: r.a, r.b, t.c
 184   Hash Cond: (t.a = r.a)
 185   Buffers: shared hit=135
 186   ->  Seq Scan on public.t (cost=0.00..145.00 rows=10000 width=8)
 187                     (actual time=0.035..1.820 rows=10000 loops=1)
 188     Output: t.a, t.c
 189     Buffers: shared hit=45
 190   ->  Hash (cost=453.59..453.59 rows=10000 width=8)
 191     (actual time=25.235..25.235 rows=10000 loops=1)
 192     Output: r.a, r.b
 193     Buckets: 16384 Batches: 1 Memory Usage: 519kB
 194     Buffers: shared hit=90
 195     ->  Hash Join (cost=172.27..453.59 rows=10000 width=8)
 196            (actual time=14.607..22.222 rows=10000 loops=1)
 197         Output: r.a, r.b
 198         Hash Cond: (r.b = s.b)
 199         Buffers: shared hit=90
 200         ->  Seq Scan on public.r (cost=0.00..145.00 rows=10000 width=8)
 201                           (actual time=0.026..1.943 rows=10000 loops=1)
 202           Output: r.a, r.b
 203           Buffers: shared hit=45
 204         ->  Hash (cost=171.01..171.01 rows=101 width=4)
 205           (actual time=14.558..14.559 rows=101 loops=1)
 206           Output: s.b
 207           Buckets: 1024 Batches: 1 Memory Usage: 12kB
 208           Buffers: shared hit=45
 209           ->  HashAggregate (cost=170.00..171.01 rows=101 width=4)
 210                      (actual time=14.423..14.486 rows=101 loops=1)
 211               Output: s.b
 212               Group Key: s.b
 213               Buffers: shared hit=45
 214               ->  Seq Scan on public.s (cost=0.00..145.00 rows=10000 width=4)
 215                                 (actual time=0.028..6.220 rows=10000 loops=1)
 216                 Output: s.b
 217                 Buffers: shared hit=45
 218 Planning time: 0.626 ms
 219 Execution time: 439.179 ms
 220 \end{verbatim}
 221
 222 It now takes only 439.7 ms to execute the query, instead of the previous 4323 ms.
 223
 224
 225 \item This was the case with (d) already.\\
 226 But if we create indices we get a merge join + hash semi join combination, which is a little bit slower.
 227 \begin{verbatim}
 228 Merge Join (cost=1118.26..12199.68 rows=1007007 width=12)
 229       (actual time=23.151..517.241 rows=987869 loops=1)
 230   Output: r.a, r.b, t.c
 231   Merge Cond: (t.a = r.a)
 232   Buffers: shared hit=585 read=29
 233   ->  Index Scan using i5 on public.t (cost=0.29..448.89 rows=10000 width=8)
 234                                (actual time=0.038..4.266 rows=10000 loops=1)
 235     Output: t.a, t.c
 236     Buffers: shared hit=495 read=29
 237   ->  Sort (cost=1117.98..1142.98 rows=10000 width=8)
 238      (actual time=23.107..186.813 rows=987870 loops=1)
 239     Output: r.a, r.b
 240     Sort Key: r.a
 241     Sort Method: quicksort Memory: 853kB
 242     Buffers: shared hit=90
 243     ->  Hash Join (cost=172.27..453.59 rows=10000 width=8)
 244            (actual time=11.223..19.180 rows=10000 loops=1)
 245         Output: r.a, r.b
 246         Hash Cond: (r.b = s.b)
 247         Buffers: shared hit=90
 248         ->  Seq Scan on public.r (cost=0.00..145.00 rows=10000 width=8)
 249                           (actual time=0.019..2.069 rows=10000 loops=1)
 250           Output: r.a, r.b
 251           Buffers: shared hit=45
 252         ->  Hash (cost=171.01..171.01 rows=101 width=4)
 253           (actual time=11.183..11.183 rows=101 loops=1)
 254           Output: s.b
 255           Buckets: 1024 Batches: 1 Memory Usage: 12kB
 256           Buffers: shared hit=45
 257           ->  HashAggregate (cost=170.00..171.01 rows=101 width=4)
 258                      (actual time=11.079..11.126 rows=101 loops=1)
 259               Output: s.b
 260               Group Key: s.b
 261               Buffers: shared hit=45
 262               ->  Seq Scan on public.s (cost=0.00..145.00 rows=10000 width=4)
 263                                 (actual time=0.019..4.724 rows=10000 loops=1)
 264                 Output: s.b
 265                 Buffers: shared hit=45
 266 Planning time: 1.320 ms
 267 Execution time: 624.356 ms
 268 \end{verbatim}
 269
 270
 271 \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);\\
 272 Without index: hash join + hash semi join
 273 \begin{verbatim}
 274 Hash Join (cost=440.25..1943.04 rows=112490 width=12)
 275     (actual time=20.751..57.164 rows=105628 loops=1)
 276 Planning time: 0.836 ms
 277 Execution time: 67.660 ms
 278 \end{verbatim}
 279
 280 With index: hash join + merge join.
 281 \begin{verbatim}
 282 Hash Join  (cost=440.59..1817.14 rows=112490 width=12)
 283      (actual time=19.917..53.721 rows=105628 loops=1)
 284 Planning time: 1.938 ms
 285 Execution time: 65.131 ms
 286 \end{verbatim}
 287
 288
 289 RESET ALL; EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT a,b,c FROM s NATURAL JOIN t WHERE a IN (SELECT r.a FROM r);
 290 Without index: hash join + hash semi join
 291 \begin{verbatim}
 292 Hash Join  (cost=442.27..56192.50 rows=4884102 width=12)
 293     (actual time=17.134..1482.398 rows=4884102 loops=1)
 294 Planning time: 0.547 ms
 295 Execution time: 1980.768 ms
 296 \end{verbatim}
 297
 298 With index: hash join + hash join.
 299 \begin{verbatim}
 300 Hash Join (cost=442.27..56192.50 rows=4884102 width=12)
 301    (actual time=17.631..1541.867 rows=4884102 loops=1)
 302 Planning time: 0.978 ms
 303 Execution time: 2040.407 ms
 304 \end{verbatim}
 305
 306 TODO: Discuss why.\\
 307 -> Indizes quasi irrelevant.\\
 308 -> performance unterschiede weil unterschiedliche set mächtigkeiten?
 309
 310 \end{enumerate}