EXCLUDE CURRENT ROW cripples performance of window functions

Question

I'm working with a customer's database where there are rows representing instances of Users, Activities (table named ActivityRecords), and ActivityUsers in correspondingly named tables, where instances of the ActivityUsers represent a particular User's involvement in a particular Activity instance.

For each record in ActivityUsers (where the user's involvement is complete and determined to be success or failure status), I'm trying to gather data about how many earlier records had success and failure statuses ('BOOKED', 'COLLECTED', 'DONE', 'CANCELED'), where those records (rows) are grouped by activity type, user role, or both, and I want to accumulate forward in time, using ActivityUser.Id as a proxy for time. I'm going to use the resulting data to train a predictive model predicting success or failure probability based on activity type and user role.

Since I don't want to use the success status of the row I'm trying to predict, only of previous records, I use ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW. If I leave that out, the sums and counts I compute are almost correct, except off by 1 if the current row's success value is 1. (ROWS UNBOUNDED PRECEDING is the default behavior. Edit, thanks to @Erwin Brandstetter: RANGE UNBOUNDED PRECEDING is the default behavior, but should work the same in my case.)

The bizarre thing is that with ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW specified, I get exponentially increasing query times based on the number of ActivityUser rows processed, but without it the query completes in about 2 seconds on the entire set of records. This is with a Postgres 12.10 server running on Azure.

There are about 200K ActivityUser rows, and about 135K of them have Status where success or failure is known. With ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW specified, and limiting the number of rows with something like WHERE act_user."Id" < 50000, I get query times like the following.

10K -> 2s
20K -> 11s
30K -> 23s
40K -> 40s
50K -> 60s

When I don't limit the record count the query runs for about 8 hours before apparently being aborted on the server. Yet if I don't limit the record count but don't use ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW, the entire query takes only 2s. It also takes only 2s with just ROWS UNBOUNDED PRECEDING.

Moreover, the query plan is the same whether I specify ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW, just ROWS UNBOUNDED PRECEDING, or nothing.

Query and query plan are below. I could also post the result of EXPLAIN ANALYZE on a 50K query if that would help.

/*
CREATE OR REPLACE FUNCTION
pg_temp.status_to_int(status text) RETURNS integer AS
$$ SELECT
  CASE status
    WHEN 'BOOKED' THEN 1
    WHEN 'COLLECTED' THEN 1
    WHEN 'DONE' THEN 1
    WHEN 'CANCELED' THEN 0
    ELSE NULL
  END
$$ language sql;
*/

EXPLAIN SELECT
  act_user."Status" status,
  act."ActivityType" typ,
  user_."Role" role_,
  act_user."Id" act_user_id,
  pg_temp.status_to_int(act_user."Status") tgt,
  coalesce(SUM(pg_temp.status_to_int(act_user."Status")) OVER w_typ, 0) typ_good,
  COUNT(*) OVER w_typ typ_all,
  coalesce(SUM(pg_temp.status_to_int(act_user."Status")) OVER w_role, 0) role_good,
  COUNT(*) OVER w_role role_all,
  coalesce(SUM(pg_temp.status_to_int(act_user."Status")) OVER w_typ_role, 0) typ_role_good,
  COUNT(*) OVER w_typ_role typ_role_all
FROM public."ActivityUsers" act_user
JOIN public."ActivityRecords" act
ON act."Id" = act_user."ActivityRecordId"
JOIN public."Users" user_
ON user_."Id" = act_user."UserId"
WHERE act_user."Status" IN ('BOOKED', 'COLLECTED', 'DONE', 'CANCELED')
WINDOW w_typ AS (PARTITION BY act."ActivityType" ORDER BY act_user."Id"
                 ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW),
       w_role AS (PARTITION BY user_."Role" ORDER BY act_user."Id"
                  ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW),
       w_typ_role AS (PARTITION BY act."ActivityType", user_."Role"
                      ORDER BY act_user."Id"
                      ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW)
/*
WINDOW w_typ AS (PARTITION BY act."ActivityType" ORDER BY act_user."Id"),
       w_role AS (PARTITION BY user_."Role" ORDER BY act_user."Id"),
       w_typ_role AS (PARTITION BY act."ActivityType", user_."Role"
                      ORDER BY act_user."Id")
*/
ORDER BY act_user_id;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=88620.91..88621.16 rows=100 width=118)
   ->  Sort  (cost=88620.91..88959.89 rows=135593 width=118)
         Sort Key: act_user."Id"
         ->  WindowAgg  (cost=77675.94..83438.64 rows=135593 width=118)
               ->  Sort  (cost=77675.94..78014.92 rows=135593 width=98)
                     Sort Key: act."ActivityType", act_user."Id"
                     ->  WindowAgg (cost=56074.11..60819.86 rows=135593 width=98)
                           ->  Sort (cost=56074.11..56413.09 rows=135593 width=82)
                                 Sort Key: act."ActivityType", user_."Role", act_user."Id"
                                 ->  WindowAgg (cost=35473.76..39880.54 rows=135593 width=82)
                                       ->  Sort (cost=35473.76..35812.75 rows=135593 width=66)
                                             Sort Key: user_."Role", act_user."Id"
                                             ->  Hash Join (cost=10583.87..19942.69 rows=135593 width=66)
                                                   Hash Cond: (act_user."UserId" = user_."Id")
                                                   ->  Hash Join  (cost=9754.17..18756.95 rows=135593 width=65)
                                                         Hash Cond: (act_user."ActivityRecordId" = act."Id")
                                                         -> Seq Scan on "ActivityUsers" act_user  (cost=0.00..5931.84 rows=135593 width=20)
Filter: ("Status" = ANY ('{BOOKED,COLLECTED,DONE,CANCELED}'::text[]))
                                                         -> Hash  (cost=7189.63..7189.63 rows=115163 width=53)
                                                              ->  Seq Scan on "ActivityRecords" act  (cost=0.00..7189.63 rows=115163 width=53)
                                                   ->  Hash (cost=667.09..667.09 rows=13009 width=9)
                                                         -> Seq Scan on "Users" user_  (cost=0.00..667.09 rows=13009 width=9) 

Answer 1

Replace:

ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW

with the equivalent:

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING

And you'll see the same performance as with ROWS UNBOUNDED PRECEDING.

EXCLUDE CURRENT ROW is a later addition to window frames (with Postgres 11) and I assume (without studying the source code) that its code path is less optimized. The clause is hardly useful in combination with the default frame end CURRENT ROW to begin with, since that can be covered with basic window frame syntax as demonstrated. But Postgres is not currently smart enough (incl. Postgres 14) to recognize and simplify the needless complication. (And I wouldn't hold my breath waiting for a change in this corner case where a simple fix is available.)

Also, the default window frame is not ROWS UNBOUNDED PRECEDING, but RANGEUNBOUNDED PRECEDING - which matters more than you might think. I published details in a related answer just yesterday:

• Find first 3 orders for each customer

There is quite a bit more that might be improved in your query, but the rest feels like paid work. :)