Does postgres optimise repeated expressions?

select column_1, (column_1 <-> cube(:input)) / (:input <-> cube(0)) difference from table_1

for this query, is calculation of :input <-> cube(0) repeated for every row?

Also, for

select calculation(row) from table_1 row where condition(row, calculation(row))

is calculation repeated in both the select, and where?

Yes, the expression will be evaluated multiple times. This is easy to test:

test=> timing
Timing is on.
test=> SELECT pg_sleep(id)
       FROM (VALUES (1), (2), (3)) AS x(id)
       WHERE pg_sleep(id) IS NOT NULL;
 pg_sleep 
══════════
 
 
 
(3 rows)

Time: 12013.768 ms (00:12.014)

If you want to avoid that, use a subquery:

test=> SELECT v
       FROM (SELECT pg_sleep(id) AS v
             FROM (VALUES (1), (2), (3)) AS x(id)) AS t
       WHERE v IS NOT NULL;
 v 
═══
 
 
 
(3 rows)

Time: 6007.817 ms (00:06.008)