By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE movies (
id SERIAL PRIMARY KEY,
title TEXT,
release_year INTEGER,
revenue DECIMAL
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE actors (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE movie_categories (
movie_id INTEGER,
category_id INTEGER,
FOREIGN KEY (movie_id) REFERENCES movies (id),
FOREIGN KEY (category_id) REFERENCES categories (id),
PRIMARY KEY (movie_id, category_id)
);
CREATE TABLE movie_tags (
movie_id INTEGER,
tag_id INTEGER,
FOREIGN KEY (movie_id) REFERENCES movies (id),
FOREIGN KEY (tag_id) REFERENCES tags (id),
PRIMARY KEY (movie_id, tag_id)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT INTO movies (title, release_year, revenue)
VALUES ('Avatar', 2009, 2787.97),
('Avengers: Endgame', 2019, 2797.8),
('Titanic', 1997, 2208.6),
('Star Wars: The Force Awakens', 2015, 2068.22),
('Avengers: Infinity War', 2018, 2048.06),
('Jurassic World', 2015, 1671.71),
('The Lion King', 2019, 1656.94),
('The Avengers', 2012, 1519),
('Furious 7', 2015, 1516.05),
('Frozen II', 2019, 1450.03);
INSERT INTO categories (name)
VALUES ('Action'),
('Romance'),
('Adventure'),
('Animation');
INSERT INTO tags (name)
VALUES ('Sci-Fi'),
('Superheroes'),
('Epic'),
('Drama');
INSERT INTO actors (name)
VALUES ('Tom Cruise'),
('Robert Downey Jr.'),
('Leonardo DiCaprio'),
('Chris Hemsworth'),
('Gal Gadot');
INSERT INTO movie_categories (movie_id, category_id)
VALUES (1, 1),
(2, 1),
(3, 2),
(4, 1),
INSERT 0 10
INSERT 0 4
INSERT 0 4
INSERT 0 5
INSERT 0 10
INSERT 0 12
INSERT 0 11
SELECT title, revenue
FROM movies
ORDER BY revenue DESC
LIMIT 5;
| title | revenue |
|---|---|
| Avengers: Endgame | 2797.8 |
| Avatar | 2787.97 |
| Titanic | 2208.6 |
| Star Wars: The Force Awakens | 2068.22 |
| Avengers: Infinity War | 2048.06 |
SELECT 5
SELECT
m.title,
m.release_year,
m.revenue,
array_to_string(array_agg(DISTINCT c.name), ', ') AS categories,
array_to_string(array_agg(DISTINCT t.name), ', ') AS tags,
array_to_string(array_agg(DISTINCT a.name), ', ') AS actors
FROM
movies m
LEFT JOIN
movie_categories mc ON m.id = mc.movie_id
LEFT JOIN
categories c ON mc.category_id = c.id
LEFT JOIN
movie_tags mt ON m.id = mt.movie_id
LEFT JOIN
tags t ON mt.tag_id = t.id
LEFT JOIN
movie_actors ma ON m.id = ma.movie_id
LEFT JOIN
actors a ON ma.actor_id = a.id
GROUP BY
m.id
ORDER BY
m.release_year DESC;
| title | release_year | revenue | categories | tags | actors |
|---|---|---|---|---|---|
| The Lion King | 2019 | 1656.94 | Animation | Drama | Leonardo DiCaprio |
| Frozen II | 2019 | 1450.03 | Animation | Epic | Gal Gadot |
| Avengers: Endgame | 2019 | 2797.8 | Action | Epic, Superheroes | Robert Downey Jr. |
| Avengers: Infinity War | 2018 | 2048.06 | Action | Epic, Superheroes | Chris Hemsworth |
| Jurassic World | 2015 | 1671.71 | Adventure | Sci-Fi | Chris Hemsworth, Tom Cruise |
| Star Wars: The Force Awakens | 2015 | 2068.22 | Action | Sci-Fi | Robert Downey Jr. |
| Furious 7 | 2015 | 1516.05 | Action | Sci-Fi | Tom Cruise |
| The Avengers | 2012 | 1519 | Action | Superheroes | Robert Downey Jr. |
| Avatar | 2009 | 2787.97 | Action | Sci-Fi | Tom Cruise |
| Titanic | 1997 | 2208.6 | Romance | Drama | Leonardo DiCaprio |
SELECT 10