Azure PostgreSQL弹性服务器中azure_pg_admin角色对查询性能的影响

我们在使用Azure PostgreSQL Flexible Server（v13）时遇到异常现象：拥有azure_pg_admin角色的用户执行查询仅需数百毫秒，无该角色的用户执行相同查询却需要5-8分钟。

关键背景信息 #

• 异常始于对部分表执行INDEX重建和FULL VACUUM操作后，此前未出现该问题。
• 已尝试为无角色用户对相关表执行ANALYZE操作，性能无改善。
• 已确认所有服务器参数在两类用户间保持一致，多数为默认值。

涉及的SQL查询语句 #

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;

拥有azure_pg_admin角色时的执行计划 #

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

无azure_pg_admin角色时的执行计划 #

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

问题根源分析 #

从执行计划可见，两类用户的逻辑执行路径完全一致，差异仅体现在实际执行耗时上——尤其是Sort和WindowAgg阶段的耗时差距极大。结合azure_pg_admin角色特性及Azure PostgreSQL环境限制，核心原因集中在以下几点：

1. 临时文件资源配额限制 #

Azure PostgreSQL Flexible Server对不同权限用户设置了临时文件（用于外部排序）的资源配额：

• azure_pg_admin角色默认拥有更高的临时存储I/O优先级或更大的临时文件配额，外部排序操作可高效利用磁盘资源完成。
• 普通用户的临时存储I/O被限制，导致外部排序（Sort Method: external merge）的磁盘读写速度极低，拖慢整个查询。

2. 角色关联的资源隔离策略 #

Azure PostgreSQL的角色权限关联底层资源池调度：

• azure_pg_admin属于管理角色，其查询被分配到优先级更高的资源队列，获得更多CPU、内存或I/O资源。
• 普通用户的查询处于低优先级队列，在INDEX重建和VACUUM后服务器缓存未完全恢复的情况下，资源竞争导致查询长时间阻塞。

3. 统计信息访问权限异常 #

虽已执行ANALYZE，但普通用户可能无法正确读取最新统计信息：

• azure_pg_admin拥有访问系统统计视图的完整权限，查询优化器能基于最新表统计生成高效执行逻辑（尽管计划文本一致，但实际执行的资源调度策略可能不同）。
• 普通用户的统计信息访问权限受限，优化器仍使用旧统计数据，导致执行时资源分配策略不合理。

4. VACUUM后的表状态访问异常 #

INDEX重建和FULL VACUUM操作可能改变表的底层状态：

• azure_pg_admin角色拥有表的完整权限，可直接访问VACUUM后整理的物理存储块；普通用户可能需额外权限验证，或无法高效访问优化后的存储结构，导致数据读取效率下降。

验证与解决建议 #

1. 检查临时存储配额：查看服务器参数temp_file_limit，确认普通用户是否被设置过低的临时文件大小限制；若允许，可临时调高该参数值测试性能变化。
2. 验证资源队列配置：检查Azure Portal中服务器的资源隔离设置，确认普通用户是否被分配到受限资源池。
3. 重新授予统计信息权限：执行GRANT pg_read_all_stats TO 普通用户名;，确保普通用户能访问系统统计视图。
4. 重新同步表权限：对remit.message表执行GRANT SELECT, INSERT, UPDATE, DELETE ON remit.message TO 普通用户名;（按需调整权限范围），并重新执行ANALYZE remit.message;。

内容的提问来源于stack exchange，提问作者Muhammad Azeem R A K