title: Postgres: The best way to optmize "greater than" query
body: <p>what is the best way to optimize join query that join with the same table on the next id value at the sub group? For now I have something like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION select_next_id(bigint, bigint) RETURNS bigint AS $body$
  DECLARE
      _id bigint;
  BEGIN
      SELECT id INTO _id FROM table WHERE id_group = $2 AND id &gt; $1 ORDER BY id ASC LIMIT 1;
      RETURN _id;
  END;
  $body$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>And the JOIN query:</p>
  
  <pre><code>SELECT * FROM table t1
  JOIN table t2 ON t2.id = select_next_id(t1.id, t1.id_group)
  </code></pre>
  
  <p>The table have more than 2kk rows, and it takes very very long. Is there a better way to do this quick? Also I have UNIQUE INDEX on column id. Not very helpfull I guess.</p>
  
  <p>Some sample data:</p>
  
  <pre>
  id | id_group
  =============
  1  | 1
  2  | 1
  3  | 1
  4  | 2
  5  | 2
  6  | 2
  20 | 4
  25 | 4
  37 | 4
  40 | 1
  55 | 2
  </pre>
  
  <p>And I want to recieve something like this:</p>
  
  <pre>
  id | id_next
  1  | 2
  2  | 3
  3  | null
  4  | 5 
  5  | 6
  6  | 55
  </pre>
  
  <p>and so on.</p>