Postgres 11

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 awards
AS
SELECT 'A' AS "Title", 'Award1' AS "Award"
UNION SELECT 'B', 'Award2'
2 rows affected

CREATE TABLE movies
AS
SELECT 'A' AS "Title", 10 AS "Rating"
UNION
SELECT 'A', 20 AS rating
UNION
SELECT 'B', 5
3 rows affected

SELECT *
FROM awards a
JOIN movies m      -- proper JOIN syntax
  ON a."Title" = m."Title"


Title
Award
Title
Rating


A
Award1
A
10

A
Award1
A
20

B
Award2
B
5


SELECT MAX(AVG(m."Rating")) OVER()
FROM awards a
JOIN movies m      -- proper JOIN syntax
  ON a."Title" = m."Title"
GROUP BY a."Award"
LIMIT 1


max


15.0000000000000000