On 1/1/16 9:39 PM, Andrew Bailey wrote:
select id, row_number() over w as rownum, lag(id, 1) over w as prev, lead(id, 1) over w as next from route where id=1350 window w as (order by shortname, id asc rows between 1 preceding and 1 following) order by shortname, id ; However this gives the result 1350;1;; The following query gives the result I am expecting select * from (select id, row_number() over w as rownum, lag(id, 1) over w as prev, lead(id, 1) over w as next from route window w as (order by shortname, id rows between 1 preceding and 1 following) order by shortname, id) as s where id=1350 1350;3;1815;1813 The explain plan is "Subquery Scan on s (cost=0.14..15.29 rows=1 width=32)" " Filter: (s.id <http://s.id> = 1350)" " -> WindowAgg (cost=0.14..13.51 rows=143 width=12)" " -> Index Only Scan using route_idx on route (cost=0.14..10.29 rows=143 width=12)"
I'm pretty sure the issue here is that the WHERE clause is limiting your result set before the window can find what you're looking for.
You could probably switch the WHERE in your original query to a HAVING and get the same results.
I'm not sure the filter can actually be pushed past the window functions to get the result you want. That Index Only Scan could still be pulling every row in the table.
BTW, if you switch the order by to id, shortname then it might be able to use the index, but of course the results would be different.
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
