normalization: explain_plan.txt

File explain_plan.txt, 10.5 KB (added by 221046, 5 weeks ago)
Line 
1#WITH OUT INDICES
2
3Hash Join (cost=104.63..105.59 rows=1 width=524) (actual time=0.254..0.258 rows=8 loops=1)
4 Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count))))
5 Buffers: shared hit=223
6 CTE playcounts
7 -> HashAggregate (cost=102.35..102.73 rows=38 width=291) (actual time=0.234..0.238 rows=15 loops=1)
8" Group Key: g.genre_id, ar.name"
9 Batches: 1 Memory Usage: 24kB
10 Buffers: shared hit=223
11 -> Hash Join (cost=91.39..102.07 rows=38 width=283) (actual time=0.189..0.224 rows=38 loops=1)
12 Hash Cond: (tr.genre_id = g.genre_id)
13 Buffers: shared hit=223
14 -> Nested Loop (cost=89.82..100.38 rows=38 width=25) (actual time=0.162..0.192 rows=38 loops=1)
15 Buffers: shared hit=222
16 -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.008..0.012 rows=1 loops=1)
17 Filter: (customer_id = 5)
18 Rows Removed by Filter: 53
19 Buffers: shared hit=3
20 -> Hash Join (cost=89.82..96.33 rows=38 width=29) (actual time=0.153..0.177 rows=38 loops=1)
21 Hash Cond: (ar.artist_id = a.artist_id)
22 Buffers: shared hit=219
23 -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.005..0.015 rows=275 loops=1)
24 Buffers: shared hit=2
25 -> Hash (cost=89.35..89.35 rows=38 width=12) (actual time=0.138..0.138 rows=38 loops=1)
26 Buckets: 1024 Batches: 1 Memory Usage: 10kB
27 Buffers: shared hit=217
28 -> Nested Loop (cost=4.91..89.35 rows=38 width=12) (actual time=0.033..0.133 rows=38 loops=1)
29 Buffers: shared hit=217
30 -> Nested Loop (cost=4.76..82.86 rows=38 width=12) (actual time=0.028..0.099 rows=38 loops=1)
31 Buffers: shared hit=141
32 -> Nested Loop (cost=4.48..67.66 rows=38 width=8) (actual time=0.022..0.047 rows=38 loops=1)
33 Buffers: shared hit=27
34 -> Bitmap Heap Scan on invoice i (cost=4.20..10.60 rows=7 width=8) (actual time=0.015..0.018 rows=7 loops=1)
35 Recheck Cond: (customer_id = 5)
36 Heap Blocks: exact=5
37 Buffers: shared hit=6
38 -> Bitmap Index Scan on invoice_customer_id_idx (cost=0.00..4.20 rows=7 width=0) (actual time=0.009..0.009 rows=7 loops=1)
39 Index Cond: (customer_id = 5)
40 Buffers: shared hit=1
41 -> Index Scan using invoice_line_invoice_id_idx on invoice_line il (cost=0.28..8.10 rows=5 width=8) (actual time=0.002..0.003 rows=5 loops=7)
42 Index Cond: (invoice_id = i.invoice_id)
43 Buffers: shared hit=21
44 -> 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)
45 Index Cond: (track_id = il.track_id)
46 Buffers: shared hit=114
47 -> 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)
48 Index Cond: (album_id = tr.album_id)
49 Buffers: shared hit=76
50 -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.023..0.023 rows=25 loops=1)
51 Buckets: 1024 Batches: 1 Memory Usage: 10kB
52 Buffers: shared hit=1
53 -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.013..0.015 rows=25 loops=1)
54 Buffers: shared hit=1
55 -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.236..0.237 rows=15 loops=1)
56 Buffers: shared hit=223
57 -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.012..0.012 rows=8 loops=1)
58 Buckets: 1024 Batches: 1 Memory Usage: 9kB
59 -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.009..0.010 rows=8 loops=1)
60 Group Key: playcounts.genre_id
61 Batches: 1 Memory Usage: 24kB
62 -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.001..0.006 rows=15 loops=1)
63Planning:
64 Buffers: shared hit=110 dirtied=1
65Planning Time: 3.223 ms
66Execution Time: 0.372 ms
67
68
69#WITH INDICES
70
71Hash Join (cost=100.63..101.59 rows=1 width=524) (actual time=0.249..0.254 rows=8 loops=1)
72 Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count))))
73 Buffers: shared hit=223
74 CTE playcounts
75 -> HashAggregate (cost=98.35..98.73 rows=38 width=291) (actual time=0.230..0.233 rows=15 loops=1)
76" Group Key: g.genre_id, ar.name"
77 Batches: 1 Memory Usage: 24kB
78 Buffers: shared hit=223
79 -> Hash Join (cost=87.39..98.07 rows=38 width=283) (actual time=0.185..0.220 rows=38 loops=1)
80 Hash Cond: (tr.genre_id = g.genre_id)
81 Buffers: shared hit=223
82 -> Nested Loop (cost=85.82..96.38 rows=38 width=25) (actual time=0.157..0.186 rows=38 loops=1)
83 Buffers: shared hit=222
84 -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.008..0.011 rows=1 loops=1)
85 Filter: (customer_id = 5)
86 Rows Removed by Filter: 53
87 Buffers: shared hit=3
88 -> Hash Join (cost=85.82..92.33 rows=38 width=29) (actual time=0.148..0.172 rows=38 loops=1)
89 Hash Cond: (ar.artist_id = a.artist_id)
90 Buffers: shared hit=219
91 -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.006..0.016 rows=275 loops=1)
92 Buffers: shared hit=2
93 -> Hash (cost=85.35..85.35 rows=38 width=12) (actual time=0.133..0.133 rows=38 loops=1)
94 Buckets: 1024 Batches: 1 Memory Usage: 10kB
95 Buffers: shared hit=217
96 -> Nested Loop (cost=4.91..85.35 rows=38 width=12) (actual time=0.031..0.127 rows=38 loops=1)
97 Buffers: shared hit=217
98 -> Nested Loop (cost=4.76..78.86 rows=38 width=12) (actual time=0.027..0.096 rows=38 loops=1)
99 Buffers: shared hit=141
100 -> Nested Loop (cost=4.48..63.66 rows=38 width=8) (actual time=0.022..0.046 rows=38 loops=1)
101 Buffers: shared hit=27
102 -> Bitmap Heap Scan on invoice i (cost=4.20..10.60 rows=7 width=8) (actual time=0.015..0.019 rows=7 loops=1)
103 Recheck Cond: (customer_id = 5)
104 Heap Blocks: exact=5
105 Buffers: shared hit=6
106 -> Bitmap Index Scan on idx_invoice_customer_id (cost=0.00..4.20 rows=7 width=0) (actual time=0.008..0.008 rows=7 loops=1)
107 Index Cond: (customer_id = 5)
108 Buffers: shared hit=1
109 -> 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.003 rows=5 loops=7)
110 Index Cond: (invoice_id = i.invoice_id)
111 Buffers: shared hit=21
112 -> 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)
113 Index Cond: (track_id = il.track_id)
114 Buffers: shared hit=114
115 -> 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)
116 Index Cond: (album_id = tr.album_id)
117 Buffers: shared hit=76
118 -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.024..0.024 rows=25 loops=1)
119 Buckets: 1024 Batches: 1 Memory Usage: 10kB
120 Buffers: shared hit=1
121 -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.015..0.017 rows=25 loops=1)
122 Buffers: shared hit=1
123 -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.231..0.232 rows=15 loops=1)
124 Buffers: shared hit=223
125 -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.013..0.013 rows=8 loops=1)
126 Buckets: 1024 Batches: 1 Memory Usage: 9kB
127 -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.009..0.011 rows=8 loops=1)
128 Group Key: playcounts.genre_id
129 Batches: 1 Memory Usage: 24kB
130 -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.001..0.006 rows=15 loops=1)
131Planning:
132 Buffers: shared hit=51
133Planning Time: 0.858 ms
134Execution Time: 0.345 ms
135