PG: Про один запрос и оконные функции

По заметке https://habrahabr.ru/post/268983/

Вполне себе мешает, вот запрос:

EXPLAIN ANALYSE
SELECT ID, lasthistoryupdate, expectedenddate
FROM tasks 
where expectedenddate is not null
ORDER BY lasthistoryupdate desc
limit 10 -- Обрати внимание

Его план:

Для ускорения - индекс

drop index if EXISTS idx_tasks_lasthistoryupdate; 

create index idx_tasks_lasthistoryupdate 
  on tasks (lasthistoryupdate desc) 
where expectedenddate is not null; 

ANALYSE tasks

Теперь пробую добавить оконную функцию row_number по другому полю:

EXPLAIN ANALYSE
SELECT ID, lasthistoryupdate, expectedenddate
, row_number() OVER (ORDER BY expectedenddate DESC) AS rating
where expectedenddate is not null
ORDER BY lasthistoryupdate desc
limit 10

План выполнения:

Ок, допускаю, что нужен индекс на внешний ордер

drop index if EXISTS idx_tasks_expectedenddate; 

create index idx_tasks_expectedenddate 
  on tasks (expectedenddate desc) 
  where expectedenddate is not null; 

ANALYSE tasks

Давай только на внутренний оставлю, пути плана же неисповедимы

На оба поля по отдельности не поможет, и даже составной:

drop index if EXISTS idx_tasks_dates; 

create index idx_tasks_dates 
  on tasks (expectedenddate desc, lasthistoryupdate desc) 
    where expectedenddate is not null; 

ANALYSE tasks

Ну и, конечно, фактический результат:

Как оптимизировать этот вариант в одном запросе - без понятия

Почему я тут не добавил доп. фильтр по датам - чтобы меньше текста было, т.к. добавляя его в оба запроса - суть не сильно изменится, а так даже наглядней - чётко видно, что происходит скан, который вызывается именно из-за оконной функции

Вот пример "оптимизации":

EXPLAIN ANALYSE
with t as (
  select ID, lasthistoryupdate, expectedenddate
  FROM tasks 
  where expectedenddate is not null
  ORDER BY lasthistoryupdate DESC
  limit 10
)
select t.*, r.rating from t
join (
    select id, row_number() OVER (ORDER BY expectedenddate DESC) AS rating 
    from tasks 
    where expectedenddate is not null
  ) r
    on t.id = r.id

Как видишь - так себе, просто меньше данных перебирается, за счёт индекса, это полезно, если подобного запроса не избежать

Но даже это ничего не даёт, если нужно сделать что-то с данными, например - заменив row_number() на sum(viewed), получается: