2

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.

1
  • 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.
    – user1593
    Mar 23, 2012 at 19:53

1 Answer 1

2

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!

2
  • 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?
    – codecool
    Mar 23, 2012 at 15:50
  • @codecool: I added a bit to my answer. Mar 23, 2012 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.