Индекс

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

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

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