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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
