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 stations(id int PRIMARY KEY, name text NOT NULL);
INSERT INTO stations VALUES 
  (1, 'Station A')
, (2, 'Station B')
, (3, 'Station C')
, (4, 'Station D')
;

CREATE TABLE rides (id int PRIMARY KEY, from_station_id int, to_station_id int);
INSERT INTO rides VALUES
  (1, 1, 2)
, (2, 1, 2)
, (3, 1, 3)
, (4, 2, 4)
;
4 rows affected

4 rows affected

-- only with actual rides
SELECT f.name AS from_station, t.name AS to_station, count
FROM  (
   SELECT from_station_id, to_station_id, count(*) AS count
   FROM   rides
   GROUP  BY 1, 2
   ) r
JOIN   stations f ON f.id = r.from_station_id
JOIN   stations t ON t.id = r.to_station_id
ORDER  BY 1, 2;  -- optional order


from_station
to_station
count


Station A
Station B
2

Station A
Station C
1

Station B
Station D
1


SELECT json_agg(sub)
FROM  (
   SELECT f.name AS from_station, t.name AS to_station, count
   FROM  (
      SELECT from_station_id, to_station_id, count(*) AS count
      FROM   rides
      GROUP  BY 1, 2
      ) r
   JOIN   stations f ON f.id = r.from_station_id
   JOIN   stations t ON t.id = r.to_station_id
   ORDER  BY 1, 2  -- optional order
   ) sub;


json_agg


[{"from_station":"Station A","to_station":"Station B","count":2}, 
 {"from_station":"Station A","to_station":"Station C","count":1}, 
 {"from_station":"Station B","to_station":"Station D","count":1}]


-- include all combinations (even without rides)
SELECT from_station, to_station, COALESCE(count, 0) AS count
FROM  (
   SELECT from_station_id, to_station_id, count(*) AS count
   FROM   rides
   GROUP  BY 1, 2
   ) r
RIGHT  JOIN (
   SELECT f.id AS from_id, f.name AS from_station
        , t.id AS to_id  , t.name AS to_station
   FROM   stations f CROSS JOIN stations t
   ) s ON  s.from_id = r.from_station_id
      AND  s.to_id   = r.to_station_id
ORDER  BY 1, 2;  -- optional order


from_station
to_station
count


Station A
Station A
0

Station A
Station B
2

Station A
Station C
1

Station A
Station D
0

Station B
Station A
0

Station B
Station B
0

Station B
Station C
0

Station B
Station D
1

Station C
Station A
0

Station C
Station B
0

Station C
Station C
0

Station C
Station D
0

Station D
Station A
0

Station D
Station B
0

Station D
Station C
0

Station D
Station D
0


SELECT json_agg(sub)
FROM  (
   SELECT from_station, to_station, COALESCE(count, 0) AS count
   FROM  (
      SELECT from_station_id, to_station_id, count(*) AS count
      FROM   rides
      GROUP  BY 1, 2
      ) r
   RIGHT  JOIN (
      SELECT f.id AS from_id, f.name AS from_station
           , t.id AS to_id  , t.name AS to_station
      FROM   stations f CROSS JOIN stations t
      ) s ON  s.from_id = r.from_station_id
         AND  s.to_id   = r.to_station_id
   ORDER  BY 1, 2  -- optional order
   ) sub;


json_agg


[{"from_station":"Station A","to_station":"Station A","count":0}, 
 {"from_station":"Station A","to_station":"Station B","count":2}, 
 {"from_station":"Station A","to_station":"Station C","count":1}, 
 {"from_station":"Station A","to_station":"Station D","count":0}, 
 {"from_station":"Station B","to_station":"Station A","count":0}, 
 {"from_station":"Station B","to_station":"Station B","count":0}, 
 {"from_station":"Station B","to_station":"Station C","count":0}, 
 {"from_station":"Station B","to_station":"Station D","count":1}, 
 {"from_station":"Station C","to_station":"Station A","count":0}, 
 {"from_station":"Station C","to_station":"Station B","count":0}, 
 {"from_station":"Station C","to_station":"Station C","count":0}, 
 {"from_station":"Station C","to_station":"Station D","count":0}, 
 {"from_station":"Station D","to_station":"Station A","count":0}, 
 {"from_station":"Station D","to_station":"Station B","count":0}, 
 {"from_station":"Station D","to_station":"Station C","count":0}, 
 {"from_station":"Station D","to_station":"Station D","count":0}]

from_station	to_station	count
Station A	Station A	0
Station A	Station B	2
Station A	Station C	1
Station A	Station D	0
Station B	Station A	0
Station B	Station B	0
Station B	Station C	0
Station B	Station D	1
Station C	Station A	0
Station C	Station B	0
Station C	Station C	0
Station C	Station D	0
Station D	Station A	0
Station D	Station B	0
Station D	Station C	0
Station D	Station D	0