Re: Efficiently selecting single row from a select with window functions row_number, lag and lead

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