Рейсы из Ульяновска

Индекс на таблице рейсов: 

=> CREATE INDEX ON flights(departure_airport);

CREATE INDEX

План запроса: 

=> EXPLAIN SELECT *
  FROM flights f JOIN airports a ON a.airport_code = f.departure_airport
  WHERE a.city = 'Ульяновск';

                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=36.43..3585.82 rows=2066 width=162)
   ->  Seq Scan on airports_data ml  (cost=0.00..30.56 rows=1 width=145)
         Filter: ((city ->> lang()) = 'Ульяновск'::text)
   ->  Bitmap Heap Scan on flights f  (cost=36.43..2491.27 rows=2066 width=63)
         Recheck Cond: (departure_airport = ml.airport_code)
         ->  Bitmap Index Scan on flights_departure_airport_idx  (cost=0.00..35.91 rows=2066 width=0)
               Index Cond: (departure_airport = ml.airport_code)
(7 rows)

Планировщик использовал соединение вложенным циклом.

Заметим, что в данном случае соединение необходимо, так как в Ульяновске два аэропорта: 

=> SELECT airport_code, airport_name FROM airports WHERE city = 'Ульяновск';

 airport_code |    airport_name     
--------------+---------------------
 ULY          | Ульяновск-Восточный
 ULV          | Баратаевка
(2 rows)

Декартово произведение 

=> EXPLAIN SELECT *
  FROM airports a1 CROSS JOIN airports a2;

                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..11067.70 rows=10816 width=198)
   ->  Seq Scan on airports_data ml  (cost=0.00..4.04 rows=104 width=145)
   ->  Materialize  (cost=0.00..4.56 rows=104 width=145)
         ->  Seq Scan on airports_data ml_1  (cost=0.00..4.04 rows=104 width=145)
(4 rows)

Соединение вложенным циклом - единственный способ выполнения таких соединений.

Узел Materialize - "материализация" выборки строк. Если выборка не превышает размер, заданный параметром work_mem, то она остается в памяти; если превышает - то записывается во временный файл. В данном случае эта операция выглядит лишней, но в следующем примере она позволяет сканировать во вложенном цикле уже отфильтрованный набор строк из a2, а не всю таблицу: 

=> EXPLAIN SELECT *
  FROM airports a1 CROSS JOIN airports a2
  WHERE a2.timezone = 'Europe/Moscow';

                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..4687.41 rows=4576 width=198)
   ->  Seq Scan on airports_data ml  (cost=0.00..4.04 rows=104 width=145)
   ->  Materialize  (cost=0.00..4.52 rows=44 width=145)
         ->  Seq Scan on airports_data ml_1  (cost=0.00..4.30 rows=44 width=145)
               Filter: (timezone = 'Europe/Moscow'::text)
(5 rows)

Таблица расстояний между аэропортами 

=> CREATE EXTENSION earthdistance CASCADE;

NOTICE:  installing required extension "cube"
CREATE EXTENSION

Чтобы отсечь повторяющиеся пары, можно соединить таблицы по условию "больше": 

=> EXPLAIN SELECT a1.airport_code "from",
      a2.airport_code "to",
      a1.coordinates <@> a2.coordinates "distance, miles"
  FROM airports a1 JOIN airports a2 ON a1.airport_code > a2.airport_code;

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.14..147.97 rows=3605 width=16)
   ->  Seq Scan on airports_data ml  (cost=0.00..4.04 rows=104 width=20)
   ->  Index Scan using airports_data_pkey on airports_data ml_1  (cost=0.14..0.95 rows=35 width=20)
         Index Cond: (ml.airport_code > airport_code)
(4 rows)

И снова вложенный цикл - единственный способ соединения для такого условия.