Here is another way to solve "big marathon" without window functions (and
many other kinds of "windowing" queries, especially those that do not
specify "rows preceeding" etc.).
It could be considered as a very dirty hack, however it could give you an
insight on the performance of the "windowed" query with indexscan instead of
seqscan.
create function var_set (text,text) returns text
as
'
select set_config (''public.''||$2||pg_backend_pid(), $1, false);
' LANGUAGE 'sql';
create function var_get (text) returns text
as
'
select current_setting(''public.''||$1||pg_backend_pid());
' LANGUAGE 'sql';
create operator >>> (procedure = var_set, leftarg = text, rightarg = text);
create operator <<< (procedure = var_get, rightarg = text);
-- init values
select ''>>>'prev_time', '0'>>>'dense_rank';
-- marathon query
select *
from (
select (((case when time::text = <<<'prev_time' then *0* else *1*
end)+(<<<'dense_rank')::int4)::text>>>'dense_rank')::int4 as position,
runnerid, time
from big_marathon
order by time
) results
where position=*2*
Best regards,
Vladimir Sitnikov
