Search Postgresql Archives

Re: Using window functions to get the unpaginated count for paginated queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.romanchenko@xxxxxxxxx> 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".


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux