| From: | Igor Romanchenko <igor(dot)a(dot)romanchenko(at)gmail(dot)com> |
|---|---|
| To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
| Cc: | "Clemens Park *EXTERN*" <clemens(dot)park(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Using window functions to get the unpaginated count for paginated queries |
| Date: | 2012-11-14 10:11:59 |
| Message-ID: | CAP95Gqne4MqA2=Pj4MdBzj0p2iJV+TfHxCfp7jcu4-ks4oauTg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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".
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Carlos Henrique Reimer | 2012-11-14 10:24:58 | Re: Running out of memory while making a join |
| Previous Message | Igor Romanchenko | 2012-11-14 09:55:59 | Re: How do query optimizers affect window functions |