Impact of azure_pg_admin Role on Query Performance in Azure PostgreSQL Flexible Server

Question

We’re using Azure PostgreSQL Flexible Server (v.13) and noticed a significant performance disparity between users that have the azure_pg_admin role and those that don’t.

For example, queries that complete in milliseconds for users with azure_pg_admin take 5-8 minutes for those without it. Could you explain why the azure_pg_admin role might have such a considerable impact on query performance? Also, how does this role affect resource allocation or other performance-related factors?

Important to note, this behavior started after performing an INDEX rebuild and a FULL VACUUM on selected tables. We did not experience this odd behavior before.

We have tried analyzing the specific tables involved for that user, but unfortunately, the performance remains unchanged. Additionally, we have cross-verified that all server parameters are consistent across users, with most settings still at their default values.

SQL Query:

explain analyze
select * from (
    select row_number() over (partition by mrid order by revision_number desc, created_date_time desc) rn, m.*
    from remit.message m 
) x
where rn = 1;

Output (with azure_pg_admin) Role:

Subquery Scan on x  (cost=12729.08..15572.52 rows=406 width=139) (actual time=530.654..723.525 rows=26717 loops=1)
  Filter: (x.rn = 1)
  Rows Removed by Filter: 54324
  ->  WindowAgg  (cost=12729.08..14557.00 rows=81241 width=139) (actual time=530.653..717.640 rows=81041 loops=1)
        ->  Sort  (cost=12729.08..12932.18 rows=81241 width=131) (actual time=530.638..664.935 rows=81041 loops=1)
              Sort Key: m.mrid, m.revision_number DESC, m.created_date_time DESC
              Sort Method: external merge  Disk: 8704kB
              ->  Seq Scan on message m  (cost=0.00..2136.41 rows=81241 width=131) (actual time=0.007..15.643 rows=81041 loops=1)
Planning Time: 0.121 ms
Execution Time: 726.626 ms

Output (without azure_pg_admin) Role:

Subquery Scan on x  (cost=12729.08..15572.52 rows=406 width=139) (actual time=209737.493..209863.031 rows=26717 loops=1)
  Filter: (x.rn = 1)
  Rows Removed by Filter: 54324
  ->  WindowAgg  (cost=12729.08..14557.00 rows=81241 width=139) (actual time=209737.492..209858.623 rows=81041 loops=1)
        ->  Sort  (cost=12729.08..12932.18 rows=81241 width=131) (actual time=209737.478..209821.675 rows=81041 loops=1)
              Sort Key: m.mrid, m.revision_number DESC, m.created_date_time DESC
              Sort Method: external merge  Disk: 8704kB
              ->  Seq Scan on message m  (cost=0.00..2136.41 rows=81241 width=131) (actual time=0.006..20.442 rows=81041 loops=1)
Planning Time: 0.593 ms
Execution Time: 406818.371 ms