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 AS
SELECT * FROM (
    VALUES 
         ('Station A'),
         ('Station B'),
         ('Station C'),
         ('Station D'),
         ('Station E')
) as s(station);

SELECT * FROM stations;

CREATE TABLE connections AS
SELECT * FROM (
    VALUES
        ('Station A', 'Station B'),
        ('Station A', 'Station E'),
        ('Station A', 'Station B'),
        ('Station A', 'Station C'),
        ('Station B', 'Station D'),
        ('Station C', 'Station C')
) as s(from_station, to_station);

SELECT * FROM connections
5 rows affected



station


Station A

Station B

Station C

Station D

Station E


6 rows affected



from_station
to_station


Station A
Station B

Station A
Station E

Station A
Station B

Station A
Station C

Station B
Station D

Station C
Station C


SELECT 
    c.from_station,
    c.to_station,
    COUNT(*)
FROM stations s1
JOIN stations s2 ON s1.station <> s2.station
JOIN connections c ON s1.station = c.from_station AND s2.station = c.to_station
GROUP BY c.from_station, c.to_station


from_station
to_station
count


Station B
Station D
1

Station A
Station E
1

Station A
Station C
1

Station A
Station B
2


SELECT 
    c.from_station,
    c.to_station,
    COUNT(*)
FROM stations s1
CROSS JOIN stations s2
JOIN connections c ON s1.station = c.from_station AND s2.station = c.to_station
GROUP BY c.from_station, c.to_station


from_station
to_station
count


Station B
Station D
1

Station C
Station C
1

Station A
Station E
1

Station A
Station C
1

Station A
Station B
2


SELECT 
    json_agg(connection)
FROM (
    SELECT 
        json_build_object(
            'fromStation', c.from_station,
            'toStation', c.to_station,
            'count', COUNT(*)
        ) as connection
    FROM stations s1
    JOIN stations s2 ON s1.station <> s2.station
    JOIN connections c ON s1.station = c.from_station AND s2.station = c.to_station
    GROUP BY c.from_station, c.to_station
) s


json_agg


[{"fromStation" : "Station B", "toStation" : "Station D", "count" : 1}, {"fromStation" : "Station A", "toStation" : "Station E", "count" : 1}, {"fromStation" : "Station A", "toStation" : "Station C", "count" : 1}, {"fromStation" : "Station A", "toStation" : "Station B", "count" : 2}]

from_station	to_station
Station A	Station B
Station A	Station E
Station A	Station B
Station A	Station C
Station B	Station D
Station C	Station C