From: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: ORDER BY, LIMIT and indexes |
Date: | 2013-08-06 23:50:49 |
Message-ID: | CAL_0b1snYfjQAGOrGs4n-ERvsMPbLvi9DT9eSgDbxmUTY9c7qQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Aug 5, 2013 at 6:54 PM, David Johnston <polobo(at)yahoo(dot)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(dot)ru(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Konoplev | 2013-08-07 00:00:22 | Re: ORDER BY, LIMIT and indexes |
Previous Message | Claudio Freire | 2013-08-06 23:09:57 | Re: ORDER BY, LIMIT and indexes |