Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY
| От | Vladimir Sitnikov |
|---|---|
| Тема | Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY |
| Дата | |
| Msg-id | 1d709ecc0811091601x4896ee72ib0c3cbf64f5ce3a6@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY (Andreas Joseph Krogh <andreak@officenet.no>) |
| Список | pgsql-hackers |
I see this as a greate feature.
I would treat ranking functions without explicit order by clause as a feature rather than a bug. However, I believe, in most cases optimizer will avoid additional sort when possible, thus an "order by" in a windowing clause would not cause any performance degradation.
It will hopefully be possible to write:
SELECT *, max(row_number()) over() as total_rows from employees;
I believe this query does not make sense. At least, "row_number" without "over" sounds odd.
To count all the rows (if you really want to) you might use "count(*) over ()".
To count all the rows (if you really want to) you might use "count(*) over ()".
To get the maximum number of rows in a separate column. Very usefull when writing queries to retrieve "paged" results. Like "Give me the 20 top articles sorted on date and also the total number of articles" in *one* query, eliminating the need for a separate count(*) query.
Sometimes it is better to perform several separate queries since optimizer could use an index scan to get "20 top" and seq scan to get the "count(*)"
Regards,
Vladimir Sitnikov
Regards,
Vladimir Sitnikov
В списке pgsql-hackers по дате отправления: