Postgres 10

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 route
( 
  route_id    INTEGER NOT NULL,
  origin      VARCHAR(2) NOT NULL,
  destination VARCHAR(2) NOT NULL, 
  -- CHECK (destination != origin) - can do it this way (remove comma)
  CONSTRAINT routes_orig_dest_distinct_ck CHECK (destination != origin)
  -- Better as it gives a meaningful name to the CONSTRAINT
  -- You can check this by swapping the CONSTRAINTs
);
 INSERT INTO route
   ( route_id, origin, destination)
 VALUES 
   ( 1, 'A', 'B' ),
   ( 2, 'C', 'D' ),
   ( 3, 'B', 'A' ),
   ( 4, 'C', 'A' ),
   ( 5, 'D', 'C' ),
   ( 5, 'B', 'T' ),  -- using B & T and H & M from the previous fiddle ( https://dbfiddle.uk/?rdbms=postgres_10&fiddle=9694a6da39405a08102193208af22a78 )
   ( 5, 'T', 'B' ),
   ( 5, 'B', 'T' ),
   ( 5, 'H', 'M' ),
   ( 5, 'H', 'M' ),
   ( 5, 'M', 'H' ),
   ( 5, 'M', 'H' ),
   ( 5, 'D', 'C' ),
   ( 5, 'D', 'C' );
14 rows affected

SELECT * FROM route;


route_id
origin
destination


1
A
B

2
C
D

3
B
A

4
C
A

5
D
C

5
B
T

5
T
B

5
B
T

5
H
M

5
H
M

5
M
H

5
M
H

5
D
C

5
D
C


SELECT 
  LEAST(origin, destination) AS point_1,
  GREATEST(origin, destination) AS point_2,
  COUNT(*) AS journey_count
FROM route
GROUP BY point_1, point_2
ORDER BY point_1, point_2;  -- The correct answer using LEAST/GREATEST


point_1
point_2
journey_count


A
B
2

A
C
1

B
T
3

C
D
4

H
M
4


-- Whereas the MIN()/MAX() option gives us
SELECT  MIN(origin) AS point_1, 
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM route
GROUP BY ASCII(origin) * ASCII(destination)  -- BUT, B & T is equivalent to H and M, therefore they are summed!
ORDER BY point_1, point_2                    -- So, the query takes the MIN of B, H, M and T with the MAX of same,
                                             -- giving us B, T  with a count of 7 (= 4 + 3, see preceding query!)


point_1
point_2
journey_count


A
B
2

B
T
7

C
A
1

C
D
4