# Индекс

Включим журналирование временных файлов и секундомер.

    => SET log_temp_files = 0;

    SET

    => \timing on

    Timing is on.

Текущее значение maintenance_work_mem:

    => SHOW maintenance_work_mem;

     maintenance_work_mem
    ----------------------
     64MB
    (1 row)

    Time: 1,005 ms

Создаем индекс:

    => CREATE INDEX ON tickets(passenger_name, passenger_id);

    CREATE INDEX
    Time: 18589,492 ms (00:18,589)

    => \timing off

    Timing is off.

Временный файл понадобился:

    postgres$ tail -n 2 /var/log/postgresql/postgresql-10-main.log

    2019-02-13 13:16:46.297 MSK [14387] postgres@demo LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14387.0", size 122847232
    2019-02-13 13:16:46.297 MSK [14387] postgres@demo STATEMENT:  CREATE INDEX ON tickets(passenger_name, passenger_id);

# Параметр cursor_tuple_fraction

План выполнения курсора:

    => EXPLAIN DECLARE c CURSOR FOR SELECT *
      FROM aircrafts a JOIN seats s ON a.aircraft_code = s.aircraft_code
      ORDER BY a.aircraft_code;

                                         QUERY PLAN
    -------------------------------------------------------------------------------------
     Merge Join  (cost=1.51..420.71 rows=1339 width=83)
       Merge Cond: (s.aircraft_code = ml.aircraft_code)
       ->  Index Scan using seats_pkey on seats s  (cost=0.28..64.60 rows=1339 width=15)
       ->  Sort  (cost=1.23..1.26 rows=9 width=52)
             Sort Key: ml.aircraft_code
             ->  Seq Scan on aircrafts_data ml  (cost=0.00..1.09 rows=9 width=52)
    (6 rows)

Текущее значение cursor_tuple_fraction:

    => SHOW cursor_tuple_fraction;

     cursor_tuple_fraction
    -----------------------
     0.1
    (1 row)

Уменьшим его:

    => SET cursor_tuple_fraction = 0.01;

    SET

    => EXPLAIN DECLARE c CURSOR FOR SELECT *
      FROM aircrafts a JOIN seats s ON a.aircraft_code = s.aircraft_code
      ORDER BY a.aircraft_code;

                                               QUERY PLAN
    ------------------------------------------------------------------------------------------------
     Merge Join  (cost=0.41..431.73 rows=1339 width=83)
       Merge Cond: (ml.aircraft_code = s.aircraft_code)
       ->  Index Scan using aircrafts_pkey on aircrafts_data ml  (cost=0.14..12.27 rows=9 width=52)
       ->  Index Scan using seats_pkey on seats s  (cost=0.28..64.60 rows=1339 width=15)
    (4 rows)

Теперь планировщик выбирает другой план: его первая компонента стоимости меньше (хотя вторая компонента наоборот, больше).