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.

• user1593: Window functions are not (generally) for limiting recordsets. they are to provide additional information based on (sub)set of the data. So, while it might be possible to use window function + wheres to emulate "limit" functionality - it's far from optimal.

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!

• codecool: I have to use limit or window functions to limit how many rows I get because at a time I only need a certain latest by date rows and get to old post only if required. How can I do that?
• Erwin Brandstetter: @codecool: I added a bit to my answer.