Re: ORDER BY, LIMIT and indexes

On Mon, Aug 5, 2013 at 6:54 PM, David Johnston <polobo@yahoo.com> wrote:
> Curious how much slower/faster these queries would run if you added:
>
> SELECT *, first_value(id) OVER (...), last_value(id) OVER (...)
> --note the window specifications need to overcome the "ORDER BY" limitation
> noted in the documentation.

To be honest I can not understand how are you going to specify partition here.

Or you are talking about wrapping the original query like this

SELECT *, first_value(id) OVER (), last_value(id) OVER () FROM (
    SELECT * FROM table
    WHERE id > :current_last_id
    ORDER BY id LIMIT 10
) AS sq2;

?

However, in this case using min()/max() instead of
fist_value()/last_value() will be faster as it does not require to do
additional scan on subquery results.

In general I do not think it would be much slower if we are not
talking about thousands of results on one page.


--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com