پیداکردن کوئری‌های پرهزینه در PostgreSQL و خواندن درست plan

همان نشست باز psql برای پایش

SELECT b.captured_at,clock_timestamp() AS observed_at,i.stats_reset=b.stats_reset AS same_reset,i.dealloc-b.dealloc AS evicted_entries FROM public.pg_stat_statements_info i CROSS JOIN academy_info_before b;
SELECT s.queryid,s.userid,s.toplevel,s.calls-b.calls AS interval_calls,round((s.total_exec_time-b.total_exec_time)::numeric,2) AS interval_ms,round(((s.total_exec_time-b.total_exec_time)/NULLIF(s.calls-b.calls,0))::numeric,2) AS ms_per_call,s.rows-b.rows AS interval_rows,s.shared_blks_read-b.shared_blks_read AS blocks_read,s.temp_blks_written-b.temp_blks_written AS temp_blocks_written
FROM public.pg_stat_statements s JOIN academy_stat_before b USING(dbid,userid,queryid,toplevel) CROSS JOIN academy_info_before ib CROSS JOIN public.pg_stat_statements_info ii
WHERE s.stats_since=b.stats_since AND ii.stats_reset=ib.stats_reset AND s.calls>b.calls AND s.total_exec_time>=b.total_exec_time
ORDER BY s.total_exec_time-b.total_exec_time DESC LIMIT 15;

کلاینت پایگاه‌دادهٔ آزمایشیِ قابل حذف

/usr/pgsql-18/bin/psql -X -v ON_ERROR_STOP=1 'service=academy_plan_lab'

همان نشست psql در پایگاه‌دادهٔ آزمایشی

SET statement_timeout='30s';
CREATE TEMP TABLE academy_plan_probe AS SELECT i AS id,(i%100)::integer AS tenant_id,CASE WHEN i%100<5 THEN 'pending' ELSE 'done' END::text AS status FROM generate_series(1,100000) AS g(i);
ANALYZE academy_plan_probe;
EXPLAIN (ANALYZE,BUFFERS,TIMING OFF,SETTINGS) SELECT count(*) FROM academy_plan_probe WHERE tenant_id=1 AND status='pending';

همان نشست psql در پایگاه‌دادهٔ آزمایشی

CREATE STATISTICS pg_temp.academy_plan_dependency(dependencies) ON tenant_id,status FROM pg_temp.academy_plan_probe;
ANALYZE academy_plan_probe;
EXPLAIN (ANALYZE,BUFFERS,TIMING OFF,SETTINGS) SELECT count(*) FROM pg_temp.academy_plan_probe WHERE tenant_id=1 AND status='pending';