Index: SQL/DDL/DDL.sql =================================================================== --- SQL/DDL/DDL.sql (revision c506097643ee3416563eb866ea22d706d1c98291) +++ SQL/DDL/DDL.sql (revision c506097643ee3416563eb866ea22d706d1c98291) @@ -0,0 +1,217 @@ +CREATE TABLE album +( + album_id INT NOT NULL, + title VARCHAR(160) NOT NULL, + artist_id INT NOT NULL, + CONSTRAINT album_pkey PRIMARY KEY (album_id) +); + +CREATE TABLE artist +( + artist_id INT NOT NULL, + name VARCHAR(120), + CONSTRAINT artist_pkey PRIMARY KEY (artist_id) +); + +CREATE TABLE customer +( + customer_id INT NOT NULL, + first_name VARCHAR(40) NOT NULL, + last_name VARCHAR(20) NOT NULL, + company VARCHAR(80), + support_rep_id INT, + contact_id INT, + address_info_id INT, + CONSTRAINT customer_pkey PRIMARY KEY (customer_id) +); + +CREATE TABLE employee +( + employee_id INT NOT NULL, + last_name VARCHAR(20) NOT NULL, + first_name VARCHAR(20) NOT NULL, + title VARCHAR(30), + reports_to INT, + birth_date TIMESTAMP, + hire_date TIMESTAMP, + contact_id INT, + address_info_id INT, + CONSTRAINT employee_pkey PRIMARY KEY (employee_id) +); + +CREATE TABLE genre +( + genre_id INT NOT NULL, + name VARCHAR(120), + CONSTRAINT genre_pkey PRIMARY KEY (genre_id) +); + +CREATE TABLE invoice +( + invoice_id INT NOT NULL, + customer_id INT NOT NULL, + invoice_date TIMESTAMP NOT NULL, + billing_address VARCHAR(70), + billing_city VARCHAR(40), + billing_state VARCHAR(40), + billing_country VARCHAR(40), + billing_postal_code VARCHAR(10), + total NUMERIC(10, 2) NOT NULL, + CONSTRAINT invoice_pkey PRIMARY KEY (invoice_id) +); + +CREATE TABLE invoice_line +( + invoice_line_id INT NOT NULL, + invoice_id INT NOT NULL, + track_id INT NOT NULL, + unit_price NUMERIC(10, 2) NOT NULL, + quantity INT NOT NULL, + CONSTRAINT invoice_line_pkey PRIMARY KEY (invoice_line_id) +); + +CREATE TABLE media_type +( + media_type_id INT NOT NULL, + name VARCHAR(120), + CONSTRAINT media_type_pkey PRIMARY KEY (media_type_id) +); + +CREATE TABLE playlist +( + playlist_id INT NOT NULL, + name VARCHAR(120), + CONSTRAINT playlist_pkey PRIMARY KEY (playlist_id) +); + +CREATE TABLE playlist_track +( + playlist_id INT NOT NULL, + track_id INT NOT NULL, + CONSTRAINT playlist_track_pkey PRIMARY KEY (playlist_id, track_id) +); + +CREATE TABLE track +( + track_id INT NOT NULL, + name VARCHAR(200) NOT NULL, + album_id INT, + media_type_id INT NOT NULL, + genre_id INT, + composer VARCHAR(220), + milliseconds INT NOT NULL, + bytes INT, + unit_price NUMERIC(10, 2) NOT NULL, + CONSTRAINT track_pkey PRIMARY KEY (track_id) +); + +CREATE TABLE price +( + price_id SERIAL PRIMARY KEY, + value NUMERIC(10, 2) NOT NULL, + date TIMESTAMP NOT NULL, + track_id INT NOT NULL +); + +CREATE TABLE Contact +( + contact_id INT, + phone VARCHAR(50), + fax VARCHAR(50), + email VARCHAR(100), + CONSTRAINT contact_pkey PRIMARY KEY (contact_id) +); + +CREATE TABLE address_info +( + address_info_id SERIAL, + address TEXT, + city TEXT, + state TEXT, + country TEXT, + postalcode TEXT, + phone TEXT, + fax TEXT, + email TEXT, + CONSTRAINT address_info_pkey PRIMARY KEY (address_info_id) +); + +ALTER TABLE album + ADD CONSTRAINT fk_album_artist FOREIGN KEY (artist_id) + REFERENCES artist(artist_id) + ON DELETE CASCADE; + +ALTER TABLE customer + ADD CONSTRAINT fk_customer_employee FOREIGN KEY (support_rep_id) + REFERENCES employee(employee_id) + ON DELETE SET NULL; + +ALTER TABLE customer + ADD CONSTRAINT fk_customer_contact FOREIGN KEY (contact_id) + REFERENCES contact(contact_id) + ON DELETE SET NULL; + +ALTER TABLE customer + ADD CONSTRAINT fk_customer_address FOREIGN KEY (address_info_id) + REFERENCES address_info(address_info_id) + ON DELETE SET NULL; + +ALTER TABLE employee + ADD CONSTRAINT fk_employee_manager FOREIGN KEY (reports_to) + REFERENCES employee(employee_id) + ON DELETE SET NULL; + +ALTER TABLE employee + ADD CONSTRAINT fk_employee_contact FOREIGN KEY (contact_id) + REFERENCES contact(contact_id) + ON DELETE SET NULL; + +ALTER TABLE employee + ADD CONSTRAINT fk_employee_address FOREIGN KEY (address_info_id) + REFERENCES address_info(address_info_id) + ON DELETE SET NULL; + +ALTER TABLE invoice + ADD CONSTRAINT fk_invoice_customer FOREIGN KEY (customer_id) + REFERENCES customer(customer_id) + ON DELETE CASCADE; + +ALTER TABLE invoice_line + ADD CONSTRAINT fk_invoice_line_invoice FOREIGN KEY (invoice_id) + REFERENCES invoice(invoice_id) + ON DELETE CASCADE; + +ALTER TABLE invoice_line + ADD CONSTRAINT fk_invoice_line_track FOREIGN KEY (track_id) + REFERENCES track(track_id) + ON DELETE CASCADE; + +ALTER TABLE track + ADD CONSTRAINT fk_track_album FOREIGN KEY (album_id) + REFERENCES album(album_id) + ON DELETE SET NULL; + +ALTER TABLE track + ADD CONSTRAINT fk_track_media FOREIGN KEY (media_type_id) + REFERENCES media_type(media_type_id) + ON DELETE RESTRICT; + +ALTER TABLE track + ADD CONSTRAINT fk_track_genre FOREIGN KEY (genre_id) + REFERENCES genre(genre_id) + ON DELETE SET NULL; + +ALTER TABLE playlist_track + ADD CONSTRAINT fk_playlist_track_playlist FOREIGN KEY (playlist_id) + REFERENCES playlist(playlist_id) + ON DELETE CASCADE; + +ALTER TABLE playlist_track + ADD CONSTRAINT fk_playlist_track_track FOREIGN KEY (track_id) + REFERENCES track(track_id) + ON DELETE CASCADE; + +ALTER TABLE price + ADD CONSTRAINT fk_price_track FOREIGN KEY (track_id) + REFERENCES track(track_id) + ON DELETE CASCADE; Index: SQL/indices/explain_plan.sql =================================================================== --- SQL/indices/explain_plan.sql (revision c506097643ee3416563eb866ea22d706d1c98291) +++ SQL/indices/explain_plan.sql (revision c506097643ee3416563eb866ea22d706d1c98291) @@ -0,0 +1,26 @@ +EXPLAIN (ANALYZE, BUFFERS) +WITH PlayCounts AS ( + SELECT + g.genre_id, + g.name AS genre_name, + ar.name AS artist_name, + COUNT(*) AS play_count + FROM customer c + JOIN invoice i ON c.customer_id = i.customer_id + JOIN invoice_line il ON i.invoice_id = il.invoice_id + JOIN track tr ON il.track_id = tr.track_id + JOIN genre g ON tr.genre_id = g.genre_id + JOIN album a ON tr.album_id = a.album_id + JOIN artist ar ON a.artist_id = ar.artist_id + WHERE c.customer_id = 5 -- example input + GROUP BY g.genre_id, g.name, ar.name +), +MaxPlayCounts AS ( + SELECT genre_id, MAX(play_count) AS max_count + FROM PlayCounts + GROUP BY genre_id +) +SELECT pc.genre_name, pc.artist_name, pc.play_count +FROM PlayCounts pc +JOIN MaxPlayCounts mpc + ON pc.genre_id = mpc.genre_id AND pc.play_count = mpc.max_count; Index: SQL/indices/index.sql =================================================================== --- SQL/indices/index.sql (revision 5e686ce10af40d42ea2bf750547dab2436169241) +++ SQL/indices/index.sql (revision c506097643ee3416563eb866ea22d706d1c98291) @@ -1,20 +1,90 @@ +DROP INDEX IF EXISTS idx_invoice_customer_id; +DROP INDEX IF EXISTS idx_invoice_line_invoice_id; +DROP INDEX IF EXISTS idx_track_genre_id; +DROP INDEX IF EXISTS idx_track_album_id; +DROP INDEX IF EXISTS idx_album_artist_id; +DROP INDEX IF EXISTS idx_genre_genreid_name; +DROP INDEX IF EXISTS idx_artist_artistid_name; +DROP INDEX IF EXISTS invoice_customer_id_idx; + + +DROP INDEX IF EXISTS album_pkey; +DROP INDEX IF EXISTS artist_pkey; +DROP INDEX IF EXISTS customer_pkey; +DROP INDEX IF EXISTS employee_pkey; +DROP INDEX IF EXISTS genre_pkey; +DROP INDEX IF EXISTS invoice_pkey; +DROP INDEX IF EXISTS invoice_line_pkey; +DROP INDEX IF EXISTS media_type_pkey; +DROP INDEX IF EXISTS playlist_pkey; +DROP INDEX IF EXISTS track_pkey; +DROP INDEX IF EXISTS playlist_track_pkey; +DROP INDEX IF EXISTS price_pkey; +DROP INDEX IF EXISTS contact_pkey; +DROP INDEX IF EXISTS playlist_track_playlist_id_idx; +DROP INDEX IF EXISTS invoice_line_track_id_idx; +DROP INDEX IF EXISTS album_artist_id_idx; +DROP INDEX IF EXISTS customer_support_rep_id_idx; +DROP INDEX IF EXISTS employee_reports_to_idx; +DROP INDEX IF EXISTS invoice_line_invoice_id_idx; +DROP INDEX IF EXISTS invoice_line_track_id_idx; +DROP INDEX IF EXISTS playlist_track_track_id_idx; +DROP INDEX IF EXISTS playlist_track_playlist_id_idx; +DROP INDEX IF EXISTS track_album_id_idx; +DROP INDEX IF EXISTS track_genre_id_idx; +DROP INDEX IF EXISTS track_media_type_id_idx; +DROP INDEX IF EXISTS track_media_type_id_idx; + +SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'invoice'; + +SELECT 'DROP INDEX IF EXISTS ' || indexname || ';' +FROM pg_indexes +WHERE tablename = 'invoice' + AND indexname NOT IN ( + SELECT conname + FROM pg_constraint + WHERE conrelid = 'invoice'::regclass + ); + +SELECT indexname +FROM pg_indexes +WHERE tablename IN ( + 'album', + 'artist', + 'customer', + 'employee', + 'genre', + 'invoice', + 'invoice_line', + 'media_type', + 'playlist', + 'playlist_track', + 'track', + 'price', + 'contact', + 'addressinfo' +); + +-- Customer → Invoice (filter + join) CREATE INDEX idx_invoice_customer_id ON invoice(customer_id); +-- Invoice → InvoiceLine (join) CREATE INDEX idx_invoice_line_invoice_id ON invoice_line(invoice_id); +-- InvoiceLine → Track (join) +CREATE INDEX idx_invoice_line_track_id + ON invoice_line(track_id); + +-- Track → Genre (join) CREATE INDEX idx_track_genre_id ON track(genre_id); +-- Track → Album (join) CREATE INDEX idx_track_album_id ON track(album_id); +-- Album → Artist (join) CREATE INDEX idx_album_artist_id ON album(artist_id); - -CREATE INDEX idx_genre_genreid_name - ON genre(genre_id, name); - -CREATE INDEX idx_artist_artistid_name - ON artist(artist_id, name); Index: explain_plan.txt =================================================================== --- explain_plan.txt (revision 5e686ce10af40d42ea2bf750547dab2436169241) +++ explain_plan.txt (revision c506097643ee3416563eb866ea22d706d1c98291) @@ -1,43 +1,107 @@ -#WITH OUT INDICES +Hash Join (cost=105.24..106.20 rows=1 width=524) (actual time=1.019..1.028 rows=8 loops=1) + Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count)))) + Buffers: shared hit=231 + CTE playcounts + -> HashAggregate (cost=102.96..103.34 rows=38 width=291) (actual time=0.981..0.987 rows=15 loops=1) +" Group Key: g.genre_id, ar.name" + Batches: 1 Memory Usage: 24kB + Buffers: shared hit=231 + -> Hash Join (cost=91.99..102.67 rows=38 width=283) (actual time=0.865..0.950 rows=38 loops=1) + Hash Cond: (tr.genre_id = g.genre_id) + Buffers: shared hit=231 + -> Nested Loop (cost=90.43..100.99 rows=38 width=25) (actual time=0.816..0.890 rows=38 loops=1) + Buffers: shared hit=230 + -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.012..0.023 rows=1 loops=1) + Filter: (customer_id = 5) + Rows Removed by Filter: 53 + Buffers: shared hit=3 + -> Hash Join (cost=90.43..96.93 rows=38 width=29) (actual time=0.802..0.861 rows=38 loops=1) + Hash Cond: (ar.artist_id = a.artist_id) + Buffers: shared hit=227 + -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.009..0.028 rows=275 loops=1) + Buffers: shared hit=2 + -> Hash (cost=89.95..89.95 rows=38 width=12) (actual time=0.772..0.774 rows=38 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 10kB + Buffers: shared hit=225 + -> Nested Loop (cost=11.59..89.95 rows=38 width=12) (actual time=0.232..0.760 rows=38 loops=1) + Buffers: shared hit=225 + -> Nested Loop (cost=11.44..83.46 rows=38 width=12) (actual time=0.222..0.691 rows=38 loops=1) + Buffers: shared hit=149 + -> Hash Join (cost=11.16..68.26 rows=38 width=8) (actual time=0.205..0.575 rows=38 loops=1) + Hash Cond: (il.invoice_id = i.invoice_id) + Buffers: shared hit=35 + -> 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) + Buffers: shared hit=29 + -> Hash (cost=11.07..11.07 rows=7 width=8) (actual time=0.056..0.056 rows=7 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + Buffers: shared hit=6 + -> Seq Scan on invoice i (cost=0.00..11.07 rows=7 width=8) (actual time=0.014..0.049 rows=7 loops=1) + Filter: (customer_id = 5) + Rows Removed by Filter: 399 + Buffers: shared hit=6 + -> 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) + Index Cond: (track_id = il.track_id) + Buffers: shared hit=114 + -> 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) + Index Cond: (album_id = tr.album_id) + Buffers: shared hit=76 + -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.040..0.041 rows=25 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 10kB + Buffers: shared hit=1 + -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.025..0.028 rows=25 loops=1) + Buffers: shared hit=1 + -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.983..0.985 rows=15 loops=1) + Buffers: shared hit=231 + -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.020..0.021 rows=8 loops=1) + Buckets: 1024 Batches: 1 Memory Usage: 9kB + -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.015..0.017 rows=8 loops=1) + Group Key: playcounts.genre_id + Batches: 1 Memory Usage: 24kB + -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.000..0.009 rows=15 loops=1) +Planning: + Buffers: shared hit=64 dirtied=1 +Planning Time: 4.045 ms +Execution Time: 1.194 ms -Hash Join (cost=104.63..105.59 rows=1 width=524) (actual time=0.254..0.258 rows=8 loops=1) + +Hash Join (cost=100.63..101.59 rows=1 width=524) (actual time=0.316..0.321 rows=8 loops=1) Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count)))) Buffers: shared hit=223 CTE playcounts - -> HashAggregate (cost=102.35..102.73 rows=38 width=291) (actual time=0.234..0.238 rows=15 loops=1) + -> HashAggregate (cost=98.35..98.73 rows=38 width=291) (actual time=0.296..0.300 rows=15 loops=1) " Group Key: g.genre_id, ar.name" Batches: 1 Memory Usage: 24kB Buffers: shared hit=223 - -> Hash Join (cost=91.39..102.07 rows=38 width=283) (actual time=0.189..0.224 rows=38 loops=1) + -> Hash Join (cost=87.39..98.07 rows=38 width=283) (actual time=0.249..0.285 rows=38 loops=1) Hash Cond: (tr.genre_id = g.genre_id) Buffers: shared hit=223 - -> Nested Loop (cost=89.82..100.38 rows=38 width=25) (actual time=0.162..0.192 rows=38 loops=1) + -> Nested Loop (cost=85.82..96.38 rows=38 width=25) (actual time=0.192..0.224 rows=38 loops=1) Buffers: shared hit=222 - -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.008..0.012 rows=1 loops=1) + -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.007..0.012 rows=1 loops=1) Filter: (customer_id = 5) Rows Removed by Filter: 53 Buffers: shared hit=3 - -> Hash Join (cost=89.82..96.33 rows=38 width=29) (actual time=0.153..0.177 rows=38 loops=1) + -> Hash Join (cost=85.82..92.33 rows=38 width=29) (actual time=0.184..0.208 rows=38 loops=1) Hash Cond: (ar.artist_id = a.artist_id) Buffers: shared hit=219 - -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.005..0.015 rows=275 loops=1) + -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.006..0.018 rows=275 loops=1) Buffers: shared hit=2 - -> Hash (cost=89.35..89.35 rows=38 width=12) (actual time=0.138..0.138 rows=38 loops=1) + -> Hash (cost=85.35..85.35 rows=38 width=12) (actual time=0.169..0.170 rows=38 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=217 - -> Nested Loop (cost=4.91..89.35 rows=38 width=12) (actual time=0.033..0.133 rows=38 loops=1) + -> Nested Loop (cost=4.91..85.35 rows=38 width=12) (actual time=0.033..0.163 rows=38 loops=1) Buffers: shared hit=217 - -> Nested Loop (cost=4.76..82.86 rows=38 width=12) (actual time=0.028..0.099 rows=38 loops=1) + -> Nested Loop (cost=4.76..78.86 rows=38 width=12) (actual time=0.028..0.132 rows=38 loops=1) Buffers: shared hit=141 - -> Nested Loop (cost=4.48..67.66 rows=38 width=8) (actual time=0.022..0.047 rows=38 loops=1) + -> Nested Loop (cost=4.48..63.66 rows=38 width=8) (actual time=0.022..0.076 rows=38 loops=1) Buffers: shared hit=27 - -> 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) + -> 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) Recheck Cond: (customer_id = 5) Heap Blocks: exact=5 Buffers: shared hit=6 - -> 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) + -> 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) Index Cond: (customer_id = 5) Buffers: shared hit=1 - -> 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) + -> 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) Index Cond: (invoice_id = i.invoice_id) Buffers: shared hit=21 @@ -48,10 +112,10 @@ Index Cond: (album_id = tr.album_id) Buffers: shared hit=76 - -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.023..0.023 rows=25 loops=1) + -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.042..0.042 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 - -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.013..0.015 rows=25 loops=1) + -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.026..0.034 rows=25 loops=1) Buffers: shared hit=1 - -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.236..0.237 rows=15 loops=1) + -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.298..0.298 rows=15 loops=1) Buffers: shared hit=223 -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.012..0.012 rows=8 loops=1) @@ -60,76 +124,8 @@ Group Key: playcounts.genre_id Batches: 1 Memory Usage: 24kB - -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.001..0.006 rows=15 loops=1) + -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.000..0.005 rows=15 loops=1) Planning: - Buffers: shared hit=110 dirtied=1 -Planning Time: 3.223 ms -Execution Time: 0.372 ms + Buffers: shared hit=49 +Planning Time: 0.894 ms +Execution Time: 0.431 ms - -#WITH INDICES - -Hash Join (cost=100.63..101.59 rows=1 width=524) (actual time=0.249..0.254 rows=8 loops=1) - Hash Cond: ((pc.genre_id = playcounts.genre_id) AND (pc.play_count = (max(playcounts.play_count)))) - Buffers: shared hit=223 - CTE playcounts - -> HashAggregate (cost=98.35..98.73 rows=38 width=291) (actual time=0.230..0.233 rows=15 loops=1) -" Group Key: g.genre_id, ar.name" - Batches: 1 Memory Usage: 24kB - Buffers: shared hit=223 - -> Hash Join (cost=87.39..98.07 rows=38 width=283) (actual time=0.185..0.220 rows=38 loops=1) - Hash Cond: (tr.genre_id = g.genre_id) - Buffers: shared hit=223 - -> Nested Loop (cost=85.82..96.38 rows=38 width=25) (actual time=0.157..0.186 rows=38 loops=1) - Buffers: shared hit=222 - -> Seq Scan on customer c (cost=0.00..3.67 rows=1 width=4) (actual time=0.008..0.011 rows=1 loops=1) - Filter: (customer_id = 5) - Rows Removed by Filter: 53 - Buffers: shared hit=3 - -> Hash Join (cost=85.82..92.33 rows=38 width=29) (actual time=0.148..0.172 rows=38 loops=1) - Hash Cond: (ar.artist_id = a.artist_id) - Buffers: shared hit=219 - -> Seq Scan on artist ar (cost=0.00..4.75 rows=275 width=25) (actual time=0.006..0.016 rows=275 loops=1) - Buffers: shared hit=2 - -> Hash (cost=85.35..85.35 rows=38 width=12) (actual time=0.133..0.133 rows=38 loops=1) - Buckets: 1024 Batches: 1 Memory Usage: 10kB - Buffers: shared hit=217 - -> Nested Loop (cost=4.91..85.35 rows=38 width=12) (actual time=0.031..0.127 rows=38 loops=1) - Buffers: shared hit=217 - -> Nested Loop (cost=4.76..78.86 rows=38 width=12) (actual time=0.027..0.096 rows=38 loops=1) - Buffers: shared hit=141 - -> Nested Loop (cost=4.48..63.66 rows=38 width=8) (actual time=0.022..0.046 rows=38 loops=1) - Buffers: shared hit=27 - -> 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) - Recheck Cond: (customer_id = 5) - Heap Blocks: exact=5 - Buffers: shared hit=6 - -> 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) - Index Cond: (customer_id = 5) - Buffers: shared hit=1 - -> 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) - Index Cond: (invoice_id = i.invoice_id) - Buffers: shared hit=21 - -> 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) - Index Cond: (track_id = il.track_id) - Buffers: shared hit=114 - -> 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) - Index Cond: (album_id = tr.album_id) - Buffers: shared hit=76 - -> Hash (cost=1.25..1.25 rows=25 width=262) (actual time=0.024..0.024 rows=25 loops=1) - Buckets: 1024 Batches: 1 Memory Usage: 10kB - Buffers: shared hit=1 - -> Seq Scan on genre g (cost=0.00..1.25 rows=25 width=262) (actual time=0.015..0.017 rows=25 loops=1) - Buffers: shared hit=1 - -> CTE Scan on playcounts pc (cost=0.00..0.76 rows=38 width=528) (actual time=0.231..0.232 rows=15 loops=1) - Buffers: shared hit=223 - -> Hash (cost=1.33..1.33 rows=38 width=12) (actual time=0.013..0.013 rows=8 loops=1) - Buckets: 1024 Batches: 1 Memory Usage: 9kB - -> HashAggregate (cost=0.95..1.33 rows=38 width=12) (actual time=0.009..0.011 rows=8 loops=1) - Group Key: playcounts.genre_id - Batches: 1 Memory Usage: 24kB - -> CTE Scan on playcounts (cost=0.00..0.76 rows=38 width=12) (actual time=0.001..0.006 rows=15 loops=1) -Planning: - Buffers: shared hit=51 -Planning Time: 0.858 ms -Execution Time: 0.345 ms -