Postgres 15

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 movie_actors (
  movie_id INTEGER,
  actor_id INTEGER,
  FOREIGN KEY (movie_id) REFERENCES movies (id),
  FOREIGN KEY (actor_id) REFERENCES actors (id),
  PRIMARY KEY (movie_id, actor_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),
       (5, 1),
       (6, 3),
       (7, 4),
       (8, 1),
       (9, 1),
       (10, 4);

INSERT INTO movie_tags (movie_id, tag_id)
VALUES (1, 1),
       (2, 2),
       (2, 3),
       (3, 4),
       (4, 1),
       (5, 2),
       (5, 3),
       (6, 1),
       (7, 4),
       (8, 2),
       (9, 1),
       (10, 3);

INSERT INTO movie_actors (movie_id, actor_id)
VALUES (1, 1),
       (2, 2),
       (3, 3),
       (4, 2),
       (5, 4),
       (6, 1),
       (6, 4),
       (7, 3),
       (8, 2),
       (9, 1),
       (10, 5);

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

title	revenue
Avengers: Endgame	2797.8
Avatar	2787.97
Titanic	2208.6
Star Wars: The Force Awakens	2068.22
Avengers: Infinity War	2048.06

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