Thanks for the reply everyone. In my case, it looks like there is no real drawback then, since what used to happen is:
SELECT a,b,c FROM table WHERE clauses OFFSET x LIMIT y; followed by: SELECT COUNT(*) FROM ( SELECT a,b,c FROM table WHERE clauses ); (notice the lack of OFFSET and LIMIT) and both of them were replaced with: SELECT COUNT(table.id) OVER () AS total_entries_count, a,b,c FROM table WHERE clauses OFFSET x LIMIT y; On Wed, Nov 14, 2012 at 5:11 AM, Igor Romanchenko < igor.a.romanche...@gmail.com> wrote: > 1) Are there any adverse effects that the above window function can have? > > It can cause severe performance degradation, as mentioned before. > > 2) Are there any cases where the count would return incorrectly? > > It could return incorrect result if there are some rows with table.id = > NULL . count(table_field) returns the number of rows, where table_field is > not NULL. > > 3) In general, is this an appropriate use-case for using window functions? > > It does the job => it is an appropriate use-case for using window > functions. > > > If this query causes performance degradation and you do not need the > exact count of rows, it is better to use something from > http://wiki.postgresql.org/wiki/Count_estimate or google for "fast > postgresql count". >
