Is GROUP BY select-list-ref less optimized than GROUP BY colname?

Question

Just wondering if GROUP BY 1 is slower than GROUP BY col?

SELECT x, count(x)
FROM foo
GROUP BY 1;

Answer 1

It demonstrably can be in the event x is not a simple column name. If it is a simple column name it doesn't matter. Take for instance this sample set.

CREATE TABLE foo
AS
  SELECT now()::date AS x
  FROM generate_series(1,1e6);

That creates a sample set of 1,000,000 rows. Now look at the following.

SELECT to_char(x,'YYYY-MM-DD'), count(*)
FROM foo
GROUP BY 1;

The above takes 820.066 ms, however..

SELECT x, count(*)
FROM foo
GROUP BY x;

The above takes only 190ms, need to get it back to the preferred format? Wrap it another SELECT

SELECT to_char(x,'YYYY-MM-DD') AS x, count
FROM (
  SELECT x, count(*)
  FROM foo
  GROUP BY x
) AS t;

And, it still just takes 190ms. Things get even more hairy if you have an index, and useful data. Then you could perhaps be visiting a table when you wouldn't otherwise need to, or you could be sorting in seq scan when an index walk would otherwise work.

It's important to note that this caveat on GROUP BY # only applies if the column of the resultset is an injective function. It's proof that it can matter, not proof that it does matter.