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

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

    => 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)

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