title: Best performance in sampling repeated value from a grouped column
body: <p>This question is about the functionality of <a href="http://www.postgresql.org/docs/devel/static/functions-window.html"><code>first_value()</code></a>, using another function or workaround.</p>
  
  <p>It is also about "little gain in performance" in big tables. To use eg. <code>max()</code> in the explained context below, demands spurious comparisons. Even if fast, it imposes some additional cost.</p>
  
  <hr>
  
  <p>This typical query</p>
  
  <pre><code>SELECT x, y, count(*) as n 
  FROM t 
  GROUP BY x, y;
  </code></pre>
  
  <p>needs to repeat all columns in <code>GROUP BY</code> to return more than one column. A syntactic sugar to do this, is to use positional references:</p>
  
  <pre><code>SELECT x, y, count(*) as n 
  FROM t 
  GROUP BY x, 2  -- imagine that 2, 3, etc. are repeated with x
  </code></pre>
  
  <p>Sometimes needs not only sugar, but also some semantic to understand complex context:</p>
  
  <pre><code>SELECT x, COALESCE(y,z), count(*) as n 
  FROM t 
  GROUP BY x, y, z  -- y and z are not "real need" grouping clauses?
  </code></pre>
  
  <p>I can imagine many other complex contexts. Let's see usual solutions:</p>
  
  <pre><code>SELECT x, max(y) as y, count(*) as n 
  FROM t 
  GROUP BY x  -- best semantic! no need for other columns here
  </code></pre>
  
  <p>where <code>max()</code> function can be any "sample()" (eg. first or last value). The performance of something that do nothing is better than <code>max()</code>, e.g. the aggregate function <code>first_value()</code>, but it needs a <code>WINDOW</code>, so lost performance. There are some old suggestions <a href="https://wiki.postgresql.org/wiki/First/last_(aggregate)">to implement first/last agg functions in C</a>.</p>
  
  <p><strong>Is there some "get any one value fast" aggregate function with better performance than <code>max()</code> or <code>GROUP BY X,2,...</code>?</strong><br>
  Perhaps some new feature in a recent release?</p>