How can we reduce the cost of a view with a Window function

Question

I have the following table structure in a Postgresql DB

• activity_planning: A table with conceptual activities (a "template" of an activity)
• scheduled_activity: a table with concrete activities that will take place
• supervised_activity: a table that links one or more supervisors with the scheduled activity

Users can plan activities weeks before, based on theoretical capacity. A few days before the activity, it becomes scheduled and somebody has to make sure the right room is available, staffing is in place, ...

Every modification on a scheduled_activity is validated in order to make sure that it's possible to facilitate. For instance a supervisor shouldn't be planned twice at the same moment.

We figured we could use window functions to quickly find the right data and we've created a View for this:

CREATE VIEW supervisor_agenda AS (
select su.supervisor_id as supervisor_id,
       lag(sch.planned_start, 1, '1970-01-01 00:00:00') over w as previous_activity_start,
       lag(act.type) over w as previous_activity_type,
       act.type as activity_type,
       lead(sch.planned_end, 1, '9999-12-31 23:59:59') over w as next_activity_end,
       lead(act.type) over w as next_activity_type,
       su.scheduled_activity_id as scheduled_activity_id,
       su.uuid as supervised_activity_id,
       sch.planned_start as activity_start,
       sch.planned_end as activity_end
from supervised_activity su
         inner join scheduled_activity sch
                    on su.scheduled_activity_id = sch.uuid
         inner join activity_planning act
                    on sch.activity_planning_id = act.uuid
    window w as (partition by su.supervisor_id order by sch.planned_start))

However it seems that for a View with Window/Aggregate functions, the predicates of a Where-clause aren't pushed down to the view.

I've put all this in a DB fiddle.

Using the query that produces the view has a cost of 83.93

select su.supervisor_id as supervisor_id,
       lag(sch.planned_start, 1, '1970-01-01 00:00:00') over w as previous_activity_start,
       lag(act.type) over w as previous_activity_type,
       act.type as activity_type,
       lead(sch.planned_end, 1, '9999-12-31 23:59:59') over w as next_activity_end,
       lead(act.type) over w as next_activity_type,
       su.scheduled_activity_id as scheduled_activity_id,
       su.uuid as supervised_activity_id,
       sch.planned_start as activity_start,
       sch.planned_end as activity_end
from supervised_activity su
         inner join scheduled_activity sch
                    on su.scheduled_activity_id = sch.uuid
         inner join activity_planning act
                    on sch.activity_planning_id = act.uuid
WHERE 
su.scheduled_activity_id = '00000000-0000-0000-0000-000000000000'
AND su.supervisor_id = '00000000-0000-0000-0000-000000000000'
    window w as (partition by su.supervisor_id order by sch.planned_start)

Using the view has a cost of 8031.20 (because there's no Index Scan)

SELECT * FROM supervisor_agenda WHERE 
scheduled_activity_id = '00000000-0000-0000-0000-000000000000'
AND supervisor_id = '00000000-0000-0000-0000-000000000000'

Is it possible to reduce the cost of the query that uses the view?

Edit: I realised that the reason the first query is much faster is because it doesn't consider all activities of a supervisor, and the window function only evaluates over one row (giving the wrong result). Still, my question remains: how could we increase the performance of the view?

Answer 1

Don't focus on actual cost values. They're useful for determining why the planner is choosing a poor plan, but not for comparing queries.

I changed your fiddle to use EXPLAIN ANALYZE instead, which is much more instructive. I also added an ANALYZE step to update the planner statistics, which cut the execution time of the view query in half (300ms down to 150ms on dbfiddle.uk).

You can see from the EXPLAIN on the view query that it is actually pushing down the predicate on supervisor_id. Since there is only one supervisor, it results in all rows being returned to be filtered later. It is still building the entire join, though; this may be due to the use of the window function.

The takeaways here:

1. Use EXPLAIN ANALYZE to compare query performance.
2. Views are a form of an optimization barrier. Related question (warning: newer PostgreSQL versions have addressed some of the earlier issues)
3. For highly selective queries, a function (in LANGUAGE SQL) might be better than a view.

Update

I loaded your fiddle into a local server and found that the raw query and the view query return different results. The view query will populate the previous_activity_start, etc. fields that use lead() and lag() while the raw query does not. You may want to review your queries and ensure they are returning the data you want.

I added a function-ized version of the view to your fiddle to demonstrate how to write a table-valued function.