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 neighborhood
(
  id SERIAL PRIMARY KEY,
  name text
);

CREATE TABLE rentals
(
   id SERIAL PRIMARY key,
   origin_id INT REFERENCES neighborhood(id),
   fare_dollars decimal
);

INSERT INTO neighborhood (NAME) VALUES ('Warsaw'), ('Cracow');

INSERT INTO rentals (origin_id, fare_dollars)
SELECT 1+floor(random()*2), 200*random()
  FROM generate_series (1, 100);
  
  
2 rows affected

100 rows affected

SELECT avg_per_neighborhood.name 
     , SUM(CASE WHEN r.fare_dollars >= avg_per_neighborhood.average_fare * 1.1 THEN 1.0 ELSE 0 END) / COUNT(*) AS percAbove110
  FROM rentals r
  JOIN (SELECT n.id, n.name, AVG(r.fare_dollars) AS average_fare
          FROM neighborhood n
          JOIN rentals r
            ON r.origin_id = n.id
         GROUP BY n.id, n.name) avg_per_neighborhood
    ON r.origin_id = avg_per_neighborhood.id
 GROUP BY avg_per_neighborhood.name
 
 


name
percabove110


Warsaw
0.40740740740740740741

Cracow
0.47826086956521739130


SELECT subq.NAME
     , SUM(subq.isHigherThan110avg) / COUNT(*) AS percAbove110
  FROM (SELECT n.NAME
             , CASE 
                 WHEN r.fare_dollars > 1.1 * AVG(r.fare_dollars) OVER (PARTITION BY n.id) THEN 1.0 
                 ELSE 0 
               END AS isHigherThan110avg
          FROM neighborhood n
          JOIN rentals r
            ON r.origin_id = n.id
     ) subq
 GROUP BY subq.name

 


name
percabove110


Warsaw
0.40740740740740740741

Cracow
0.47826086956521739130