Working of window functions and idea window size for window function

Question

I am not able to understand the concept of window functions. How exactly they work and what are the pros and cons of such technique?

I have read that using limit and offset is slow but its still used. So decision between limit/offset and window functions depend upon size of data to get from the db or window functions should always be used?

What is ideal window size to get maximum performance from this? Can it be the case where if number of rows to get is less then simply use limit/offset?

My use case basically requires sorting data on date and then bring the latest data first. How window functions fit in this scenario.

Answer 1

The elementary difference is that window functions are applied to all rows in a result set to compute additional columns after the rest of the result set has been determined. No row is dropped. They are available since PostgreSQL 8.4.

The LIMIT and OFFSET clauses of the SELECT command on the other hand do not compute additional columns. They just pick a certain "window" of rows from the result set (in cooperation with the ORDER BY clause) and discard the rest. Have been there like for ever.

While certain tasks can be tackled with either of these tools, they are very different in nature.

For your simple task

sorting data on date and then bring the latest data first

you don't need either of them. Just add:

ORDER BY date_col DESC

---

According to your comment, you would need:

SELECT col1, col2
FROM   tbl
ORDER  BY date_col DESC
LIMIT  100   -- 100 latest rows
OFFSET 0;    -- just noise, but may be easier to code

Retrieve more:

...
LIMIT  100
OFFSET 100;  -- next page of 100 rows ...

Be sure to have an index on date_col in either case!