postgresql window functions count

Question

I can this query SELECT COUNT(*) OVER(), id, name, date... FROM table WHERE user_id = 1 LIMIT 20, OFFSET 0 I need the count for pagination, how does this work? does count gets computed for each row or just a single time?

Does it affects index uses, as I know functions can sometimes affect index uses but mostly in where clauses.

Are there any performance penalties of doing it like this instead of the 2 separate queries (count and then data retrieve)? I would not do a sub query as that will run for each row.

Thanks

Answer 1

Using a window function is more efficient than running two queries. There is no possible influence on index usage.

Window functions are calculated after the result set has been generated, but before LIMIT and OFFSET. The result is added as an extra column to each row.

I cannot see how the total count you are calculating with this window function will be useful for pagination.

As an aside, using LIMIT and OFFSET is pointless without an ORDER BY clause.