1

I have the following table structure in a Postgresql DB SQL table layout

  • 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?

1 Answer 1

0

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.

4
  • Thanks for your suggestions. I don't think you can edit my fiddle, if you want to share changes, you'll need to copy the url of your last version. The results of the queries are indeed different and I shouldn't compare those costs (see the edit in my post). I've considered a function but in reality we're using far more predicates than described above.
    – TomVW
    Sep 7, 2021 at 18:12
  • My changes are in this fiddle then. As far as the view query itself, not sure there's a lot that can be done, unless you can reduce the amount of data or processing required. If you can get rid of the lead/lag calls then the whole result set isn't needed. What kind of performance are you seeing in the production version?
    – dwhitemv
    Sep 7, 2021 at 18:57
  • I'm not worried about the performance today, but the Seq Scan in the query plan will keep on increasing. I could partition the table and move partitions out when they're no longer relevant. Or split this into two views: with and without the window function. I was hoping for an easier solution :)
    – TomVW
    Sep 8, 2021 at 9:50
  • Eh, nothing easy in what you’ve shown. partitioning seems like a good fit here (old events can be archived). Your indexes look to support queries for individual objects (I.e. supervisor_id).
    – dwhitemv
    Sep 8, 2021 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.