Как заставить Postgres вычислять значения в колонках после сортировки?

Question

detinkin @detinkin

Как заставить Postgres вычислять значения в колонках после сортировки?

У меня есть большой запрос с множеством вычисляемых колонок в SELECT. Также у этого запроса есть сортировка по одному из вычисляемых колонок и ограничение на получение первых 100 строк. Так вот, Postgres зачем-то вычисляет значения колонок для всех строк, а не только для первых 100. Я понимаю, что он должен вычислить для всех строк ту колонку, по которой сделана сортировка - это логично. Но зачем вычислять все остальные?
Объясню на примере.
Создадим тестовую таблицу
CREATE TABLE test_main(col1 INTEGER);
И заполним ее случайными данными

DO
$do$
BEGIN
  FOR r IN 1..100000 LOOP
    INSERT INTO test_main(col1) VALUES (trunc(random()*1000));
  END LOOP;
END
$do$;

Потом создадим две дополнительные таблицы

CREATE TABLE test_main_agg1(
  col1 INTEGER,
  val INTEGER
);
CREATE TABLE test_main_agg2(
  col1 INTEGER,
  val INTEGER
);

Их тоже заполним

DO
$do$
DECLARE
 r test_main%rowtype;
BEGIN
  FOR r IN SELECT * FROM test_main LOOP
    FOR i IN 1..5 LOOP
      INSERT INTO test_main_agg1(col1, val) VALUES (r.col1, trunc(random()*1000));
      INSERT INTO test_main_agg2(col1, val) VALUES (r.col1, trunc(random()*1000));
    END LOOP;
  END LOOP;
END
$do$;

И конечно создадим индексы

CREATE INDEX test_main_indx ON test_main(col1);
CREATE INDEX test_main_agg1_val_indx ON test_main_agg1(col1,val);
CREATE INDEX test_main_agg2_val_indx ON test_main_agg2(col1,val);

А вот теперь, попробуем выполнить такой запрос:

SELECT col1,
       (SELECT MAX(val) FROM test_main_agg1 g WHERE g.col1=m.col1) max_val1,
       (SELECT MAX(val) FROM test_main_agg2 g WHERE g.col1=m.col1) max_val2
  FROM test_main m
 LIMIT 100;

Запрос выполнится быстро благодаря индексам. Если мы к запросу добавим ORDER BY col1, то он по прежнему будет выполняться быстро. Однако, если мы добавим ORDER BY max_val1, то выполнение займет порядка двух секунд. Я смотрел в EXPLAIN ANALYZE и увидел такие строки:

SubPlan 4
 -> Result (cost=4.06..4.07 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=100000)
  InitPlan 3 (returns $3)
   -> Limit (cost=0.42..4.06 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=100000)
    -> Index Only Scan Backward using test_main_agg2_val_indx on test_main_agg2 g_1 (cost=0.42..1818.25 rows=500 width=4) (actual time=0.010..0.010 rows=1 loops=100000)
     Index Cond: ((col1 = m.col1) AND (val IS NOT NULL))
     Heap Fetches: 100000

То есть Postgres вычисляет значение max_val2 для всех 100000 строк, а не для первых 100. Зачем? Ведь сортировка идет по полю max_val1 и для 100000 строк достаточно вычислить только его?

Может у Postgres есть какие-нибудь хинты или чтото подобное, чтобы сказать ему вычислять значения столбцов после применения ORDER BY и LIMIT?