title: How to make Postgres calculate columns after ordering?
body: <p>I have some big sql query with many calculating columns in <code>SELECT</code> block. Also, there is ordering by one of those calculating columns and limit for only 100 rows. But postgres calculates all columns for every row, not for only 100.</p>
  
  <p>Let me explain on example.</p>
  
  <p>Let's create some test table:</p>
  
  <pre><code>CREATE TABLE test_main(col1 INTEGER);
  </code></pre>
  
  <p>And fill it with some random data:</p>
  
  <pre><code>DO
  $do$
  BEGIN
    FOR r IN 1..100000 LOOP
      INSERT INTO test_main(col1) VALUES (trunc(random()*1000));
    END LOOP;
  END
  $do$;
  </code></pre>
  
  <p>Then create some additional tables:</p>
  
  <pre><code>CREATE TABLE test_main_agg1(
    col1 INTEGER,
    val INTEGER
  );
  CREATE TABLE test_main_agg2(
    col1 INTEGER,
    val INTEGER
  );
  </code></pre>
  
  <p>And fill it too:</p>
  
  <pre><code>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$;
  </code></pre>
  
  <p>And, of course, create some indexes:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Now, if we execute this query:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>It will be very fast because of indexes. If we add <code>ORDER BY col1</code> it is still going to be fast. But if we will use <code>ORDER BY max_val1</code>, then it will take about 2 seconds.
  If we run <code>EXPLAIN ANALYZE</code> on query with `ORDER BY max_val1, we will see this rows:</p>
  
  <pre><code>SubPlan 4
   -&gt; Result (cost=4.06..4.07 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=100000)
    InitPlan 3 (returns $3)
     -&gt; Limit (cost=0.42..4.06 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=100000)
      -&gt; 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
  </code></pre>
  
  <p>It means, that postgres calculate <code>max_val2</code> for 100000 rows, but not for only 100 rows. I inderstand why postgres needs to calculate <code>max_val1</code>, but not <code>max_val2</code>.</p>
  
  <p>Maybe there is some hints or something like this to tell postgres calculate columns after it execute ordering and limit?</p>