Postgres 13

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?.

CREATE TABLE movie (
  id int PRIMARY KEY
, yr int
);

CREATE TABLE actor (
  id int PRIMARY KEY
, name text NOT NULL -- assuming name unique?
);

CREATE TABLE casting (
  movieid int REFERENCES movie
, actorid int REFERENCES actor
, PRIMARY KEY (movieid, actorid)
);

INSERT INTO actor VALUES
  (1, 'Buster Keaton')
, (2, 'John Travolta')
, (3, 'Harvey Keitel')
;

INSERT INTO movie VALUES
  ( 1, '1976')
, ( 2, '1976')
, ( 3, '1977')
, ( 4, '1977')
, ( 5, '1978')
, ( 6, '1978')
, ( 7, '1981')
, ( 8, '1981')
, ( 9, '1994')
, (10, '1994')
, (11, '1989')
, (12, '1999')
;

INSERT INTO casting(actorid, movieid) VALUES
  (1, 1)
, (1, 12)
, (2, 1)
, (2, 2)
, (2, 3)
, (2, 4)
, (2, 5)
, (2, 6)
, (2, 7)
, (2, 8)
, (2, 9)
, (2, 10)
, (2, 11)
, (3, 11)
;

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 12



INSERT 0 14


-- Postgres 13: WITH TIES
SELECT m.yr, count(*) AS movie_count
FROM   casting c
JOIN   movie   m ON c.movieid = m.id
WHERE  c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
GROUP  BY m.yr
ORDER  BY count(*) DESC  -- can't sort by year for this
FETCH  FIRST 1 ROWS WITH TIES;


yr
movie_count


1976
2

1977
2

1978
2

1981
2

1994
2



SELECT 5


-- Older versions: with rank() in subquery
SELECT yr, movie_count
FROM  (
   SELECT m.yr, count(*) AS movie_count
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   casting c
   JOIN   movie   m ON c.movieid = m.id
   WHERE  c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
   GROUP  BY m.yr
   ) sub
WHERE  rnk = 1
ORDER  BY yr;  -- optionally sort by year


yr
movie_count


1976
2

1977
2

1978
2

1981
2

1994
2



SELECT 5


-- There would be more year, but with fewer movies:
SELECT m.yr, count(*) AS movie_count
FROM   casting c
JOIN   movie   m ON c.movieid = m.id
WHERE  c.actorid = (SELECT id FROM actor WHERE name = 'John Travolta')
GROUP  BY m.yr
ORDER  BY count(*) DESC;


yr
movie_count


1976
2

1977
2

1978
2

1981
2

1994
2

1989
1



SELECT 6