normalization: explain_plan2.txt

File explain_plan2.txt, 10.4 KB (added by 221046, 8 days ago)
Line 
1Hash Join (cost=105.24..106.20 rows=1 width=524) (actual time=1.019..1.028 rows=8 loops=1)
2 Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count))))
3 Buffers: shared hit=231
4 CTE playcounts
5 -> HashAggregate (cost=102.96..103.34 rows=38 width=291) (actual time=0.981..0.987 rows=15 loops=1)
6" Group Key: g.genre_id, ar.name"
7 Batches: 1 Memory Usage: 24kB
8 Buffers: shared hit=231
9 -> Hash Join (cost=91.99..102.67 rows=38 width=283) (actual time=0.865..0.950 rows=38 loops=1)
10 Hash Cond: (tr.genre_id = g.genre_id)
11 Buffers: shared hit=231
12 -> Nested Loop (cost=90.43..100.99 rows=38 width=25) (actual time=0.816..0.890 rows=38 loops=1)
13 Buffers: shared hit=230
14 -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.012..0.023 rows=1 loops=1)
15 Filter: (customer_id = 5)
16 Rows Removed by Filter: 53
17 Buffers: shared hit=3
18 -> Hash Join (cost=90.43..96.93 rows=38 width=29) (actual time=0.802..0.861 rows=38 loops=1)
19 Hash Cond: (ar.artist_id = a.artist_id)
20 Buffers: shared hit=227
21 -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.009..0.028 rows=275 loops=1)
22 Buffers: shared hit=2
23 -> Hash (cost=89.95..89.95 rows=38 width=12) (actual time=0.772..0.774 rows=38 loops=1)
24 Buckets: 1024 Batches: 1 Memory Usage: 10kB
25 Buffers: shared hit=225
26 -> Nested Loop (cost=11.59..89.95 rows=38 width=12) (actual time=0.232..0.760 rows=38 loops=1)
27 Buffers: shared hit=225
28 -> Nested Loop (cost=11.44..83.46 rows=38 width=12) (actual time=0.222..0.691 rows=38 loops=1)
29 Buffers: shared hit=149
30 -> Hash Join (cost=11.16..68.26 rows=38 width=8) (actual time=0.205..0.575 rows=38 loops=1)
31 Hash Cond: (il.invoice_id = i.invoice_id)
32 Buffers: shared hit=35
33 -> Seq Scan on invoice_line il (cost=0.00..51.21 rows=2221 width=8) (actual time=0.010..0.324 rows=2221 loops=1)
34 Buffers: shared hit=29
35 -> Hash (cost=11.07..11.07 rows=7 width=8) (actual time=0.056..0.056 rows=7 loops=1)
36 Buckets: 1024 Batches: 1 Memory Usage: 9kB
37 Buffers: shared hit=6
38 -> Seq Scan on invoice i (cost=0.00..11.07 rows=7 width=8) (actual time=0.014..0.049 rows=7 loops=1)
39 Filter: (customer_id = 5)
40 Rows Removed by Filter: 399
41 Buffers: shared hit=6
42 -> Index Scan using track_pkey on track tr (cost=0.28..0.40 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=38)
43 Index Cond: (track_id = il.track_id)
44 Buffers: shared hit=114
45 -> Index Scan using album_pkey on album a (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=38)
46 Index Cond: (album_id = tr.album_id)
47 Buffers: shared hit=76
48 -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.040..0.041 rows=25 loops=1)
49 Buckets: 1024 Batches: 1 Memory Usage: 10kB
50 Buffers: shared hit=1
51 -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.025..0.028 rows=25 loops=1)
52 Buffers: shared hit=1
53 -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.983..0.985 rows=15 loops=1)
54 Buffers: shared hit=231
55 -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.020..0.021 rows=8 loops=1)
56 Buckets: 1024 Batches: 1 Memory Usage: 9kB
57 -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.015..0.017 rows=8 loops=1)
58 Group Key: playcounts.genre_id
59 Batches: 1 Memory Usage: 24kB
60 -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.000..0.009 rows=15 loops=1)
61Planning:
62 Buffers: shared hit=64 dirtied=1
63Planning Time: 4.045 ms
64Execution Time: 1.194 ms
65
66
67Hash Join (cost=100.63..101.59 rows=1 width=524) (actual time=0.316..0.321 rows=8 loops=1)
68 Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count))))
69 Buffers: shared hit=223
70 CTE playcounts
71 -> HashAggregate (cost=98.35..98.73 rows=38 width=291) (actual time=0.296..0.300 rows=15 loops=1)
72" Group Key: g.genre_id, ar.name"
73 Batches: 1 Memory Usage: 24kB
74 Buffers: shared hit=223
75 -> Hash Join (cost=87.39..98.07 rows=38 width=283) (actual time=0.249..0.285 rows=38 loops=1)
76 Hash Cond: (tr.genre_id = g.genre_id)
77 Buffers: shared hit=223
78 -> Nested Loop (cost=85.82..96.38 rows=38 width=25) (actual time=0.192..0.224 rows=38 loops=1)
79 Buffers: shared hit=222
80 -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.007..0.012 rows=1 loops=1)
81 Filter: (customer_id = 5)
82 Rows Removed by Filter: 53
83 Buffers: shared hit=3
84 -> Hash Join (cost=85.82..92.33 rows=38 width=29) (actual time=0.184..0.208 rows=38 loops=1)
85 Hash Cond: (ar.artist_id = a.artist_id)
86 Buffers: shared hit=219
87 -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.006..0.018 rows=275 loops=1)
88 Buffers: shared hit=2
89 -> Hash (cost=85.35..85.35 rows=38 width=12) (actual time=0.169..0.170 rows=38 loops=1)
90 Buckets: 1024 Batches: 1 Memory Usage: 10kB
91 Buffers: shared hit=217
92 -> Nested Loop (cost=4.91..85.35 rows=38 width=12) (actual time=0.033..0.163 rows=38 loops=1)
93 Buffers: shared hit=217
94 -> Nested Loop (cost=4.76..78.86 rows=38 width=12) (actual time=0.028..0.132 rows=38 loops=1)
95 Buffers: shared hit=141
96 -> Nested Loop (cost=4.48..63.66 rows=38 width=8) (actual time=0.022..0.076 rows=38 loops=1)
97 Buffers: shared hit=27
98 -> Bitmap Heap Scan on invoice i (cost=4.20..10.60 rows=7 width=8) (actual time=0.015..0.038 rows=7 loops=1)
99 Recheck Cond: (customer_id = 5)
100 Heap Blocks: exact=5
101 Buffers: shared hit=6
102 -> Bitmap Index Scan on idx_invoice_customer_id (cost=0.00..4.20 rows=7 width=0) (actual time=0.009..0.009 rows=7 loops=1)
103 Index Cond: (customer_id = 5)
104 Buffers: shared hit=1
105 -> Index Scan using idx_invoice_line_invoice_id on invoice_line il (cost=0.28..7.53 rows=5 width=8) (actual time=0.002..0.005 rows=5 loops=7)
106 Index Cond: (invoice_id = i.invoice_id)
107 Buffers: shared hit=21
108 -> Index Scan using track_pkey on track tr (cost=0.28..0.40 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=38)
109 Index Cond: (track_id = il.track_id)
110 Buffers: shared hit=114
111 -> Index Scan using album_pkey on album a (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=38)
112 Index Cond: (album_id = tr.album_id)
113 Buffers: shared hit=76
114 -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.042..0.042 rows=25 loops=1)
115 Buckets: 1024 Batches: 1 Memory Usage: 10kB
116 Buffers: shared hit=1
117 -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.026..0.034 rows=25 loops=1)
118 Buffers: shared hit=1
119 -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.298..0.298 rows=15 loops=1)
120 Buffers: shared hit=223
121 -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.012..0.012 rows=8 loops=1)
122 Buckets: 1024 Batches: 1 Memory Usage: 9kB
123 -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.009..0.010 rows=8 loops=1)
124 Group Key: playcounts.genre_id
125 Batches: 1 Memory Usage: 24kB
126 -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.000..0.005 rows=15 loops=1)
127Planning:
128 Buffers: shared hit=49
129Planning Time: 0.894 ms
130Execution Time: 0.431 ms
131