Postgresql Writing max() Window function with multiple partition expressions?
The complexity of the window functions partitioning clause should not have a big impact on performance. Do realize that your query is returning all the rows in the table, so there might be a very large result set.
Window functions should be able to take advantage of indexes. For this query:
SELECT address_token, list_date, original_list_price, max(original_list_price) OVER (PARTITION BY address_token, list_date) as max_list_priceFROM table1;You want an index on table1(address_token, list_date, original_list_price).
You could try writing the query as:
select t1.*, (select max(t2.original_list_price) from table1 t2 where t2.address_token = t1.address_token and t2.list_date = t1.list_date ) as max_list_pricefrom table1 t1;This should return results more quickly, because it doesn't have to calculate the window function value first (for all rows) before returning values.