【案例】存储过程慢

1. 查询活跃会话定位到存储过程

select * from pgxc_stat_activity where state = 'active' and query like '%%' and usename not in ('omm', 'Ruby');

2.根据queryid查询等待视图

select * from pgxc_thread_wait_status where query_id=xxx;

等待视图中未发现持锁
3.通过lwtid打堆栈

gstack lwtid;

持续观察堆栈情况，该堆栈内容持续变化，说明当前非死锁。是业务慢导致。

4.对涉及到的表做analyze，拼接analyze命令

select 'analyze ' ||schemaname|| ‘.’ ||tablename || ';' from PG_TABLES where tableowner in (xxx);

5.该集群已开启TOPSQL，开启TOPSQL子查询功能

gs_guc reload -Z coordinator -N all -I all -c "enable_track_record_subsql = on"

6.查询实时TOPSQL，找到该存储过程

\x
select * from pgxc_wlm_session_statistics where query like '%%';

7.根据queryid查询实时TOPSQL，观察存储过程子语句中执行时间过长的语句

select * from pgxc_wlm_session_statistics where queryid = xxx;

8.观察到有not in 这种效率不佳的写法，建议改为not exist。