Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uURWy-003MOv-2Y for pgsql-admin@arkaria.postgresql.org; Wed, 25 Jun 2025 14:55:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uURWw-004OwV-5C for pgsql-admin@arkaria.postgresql.org; Wed, 25 Jun 2025 14:55:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uURWv-004OwA-QK for pgsql-admin@lists.postgresql.org; Wed, 25 Jun 2025 14:55:18 +0000 Received: from mx126.mail.hosttech.eu ([82.220.38.13] helo=126.hosttech.eu) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1uURWu-003wR5-11 for pgsql-admin@lists.postgresql.org; Wed, 25 Jun 2025 14:55:17 +0000 X-Spam-Status: No X-hosttech-MailScanner-From: raphi@crashdump.ch X-hosttech-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=-2.7, required 5, ALL_TRUSTED -1.00, BAYES_00 -1.90, HT_185 0.20) X-hosttech-MailScanner: Found to be clean X-hosttech-MailScanner-ID: AA78C375402F.A668C X-hosttech-MailScanner-Information: Please contact the ISP for more information Received: from [192.168.1.205] (31-10-141-228.cgn.dynamic.upc.ch [31.10.141.228]) by 126.hosttech.eu (Postfix) with ESMTPSA id AA78C375402F for ; Wed, 25 Jun 2025 16:55:06 +0200 (CEST) Message-ID: <67c4e192-d928-481a-81cf-7080c2e79381@crashdump.ch> Date: Wed, 25 Jun 2025 16:55:07 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird To: pgsql-admin@lists.postgresql.org X-hosttech-server: 126.hosttech.eu From: raphi Subject: pgstat_snap - create adhoc pg_stat_statements snapshots Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi all, I've created a script which helps me as a DBA to trace down performance=20 problems, especially on clusters with multiple databases installed. It=20 creates timestamped copies of pg_stat_statements and pg_stat_activity=20 and provides two views that contain the execution delta of every=20 query/dbid between timestamps, e.g. how many rows were affected. Basic=20 workflow is: - Install: pgsql \i pgstat_snap.sql - collect, e.g. every 1 second 60 times: CALL pgstat_snap.create_snapshot(1, 60); - analyze - the _d columsn are the difference between snapshots select * from pgstat_snap_diff order by 1; snapshot_time=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0queryid=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0query =C2=A0 =C2=A0datna= me=C2=A0=20 usename wait_event_type wait_event=C2=A0=C2=A0=C2=A0=C2=A0 rows_d=C2=A0 =C2= =A0calls_d =C2=A0 exec_ms_d=20 sb_hit_d sb_read_d sb_dirt_d sb_write_d 2025-03-25 11:00:19=C2=A0=C2=A0=C2=A0=C2=A0 4380144606300689468=C2=A0=C2=A0= =C2=A0=C2=A0 UPDATE pgbench_tell=C2=A0=C2=A0=C2=A0=C2=A0=20 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 tr= ansactionid 4485=C2=A0=C2=A0=C2=A0=C2=A0 4485=C2=A0=C2=A0=C2=A0=C2=A0=20 986.262098=C2=A0=C2=A0=C2=A0=C2=A0 22827=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A00=C2=A0=C2=A0=C2=A0=C2=A0 0 2025-03-25 11:00:20=C2=A0=C2=A0=C2=A0=C2=A0 4380144606300689468=C2=A0=C2=A0= =C2=A0=C2=A0 UPDATE pgbench_tell=C2=A0=C2=A0=C2=A0=C2=A0=20 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 tr= ansactionid 1204=C2=A0=C2=A0=C2=A0=C2=A0 1204=C2=A0=C2=A0=C2=A0=C2=A0=20 228.822413=C2=A0=C2=A0=C2=A0=C2=A0 6115=C2=A0 =C2=A0 =C2=A0 0=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A00=C2=A0=C2=A0=C2=A0=C2=A0 0 2025-03-25 11:00:20=C2=A0=C2=A0=C2=A0=C2=A0 7073332947325598809=C2=A0=C2=A0= =C2=A0=C2=A0 UPDATE pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0=20 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 tr= ansactionid 1204=C2=A0=C2=A0=C2=A0=C2=A0 1204=C2=A0=C2=A0=C2=A0=C2=A0=20 1758.190499=C2=A0=C2=A0=C2=A0=C2=A0 5655=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A00=C2=A0=C2=A0=C2=A0=C2=A0 0 2025-03-25 11:00:21=C2=A0=C2=A0=C2=A0=C2=A0 7073332947325598809=C2=A0=C2=A0= =C2=A0=C2=A0 UPDATE pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0=20 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 tr= ansactionid 1273=C2=A0=C2=A0=C2=A0=C2=A0 1273=C2=A0=C2=A0=C2=A0=C2=A0=20 2009.227575=C2=A0=C2=A0=C2=A0=C2=A0 6024=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A00=C2=A0=C2=A0=C2=A0=C2=A0 0 2025-03-25 11:00:22=C2=A0=C2=A0=C2=A0=C2=A0 2931033680287349001=C2=A0=C2=A0= =C2=A0=C2=A0 UPDATE pgbench_acco=C2=A0=C2=A0=C2=A0=C2=A0=20 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0 Client=C2=A0=C2=A0=C2=A0=C2=A0 = ClientRead 9377=C2=A0=C2=A0=C2=A0=C2=A0 9377=C2=A0=C2=A0=C2=A0=C2=A0=20 1818.464415=C2=A0=C2=A0=C2=A0=C2=A0 66121=C2=A0=C2=A0=C2=A0=C2=A0 3699=C2= =A0=C2=A0=C2=A0=C2=A0 7358=C2=A0 =C2=A035 2025-03-25 11:00:22=C2=A0=C2=A0=C2=A0=C2=A0 7073332947325598809=C2=A0=C2=A0= =C2=A0=C2=A0 UPDATE pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0=20 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 tr= ansactionid 1356=C2=A0=C2=A0=C2=A0=C2=A0 1356=C2=A0=C2=A0=C2=A0=C2=A0=20 1659.806856=C2=A0=C2=A0=C2=A0=C2=A0 6341=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A00=C2=A0=C2=A0=C2=A0=C2=A0 0 2025-03-25 11:00:23=C2=A0=C2=A0=C2=A0=C2=A0 7073332947325598809=C2=A0=C2=A0= =C2=A0=C2=A0 UPDATE pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0=20 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 tr= ansactionid 1168=C2=A0=C2=A0=C2=A0=C2=A0 1168=C2=A0=C2=A0=C2=A0=C2=A0=20 1697.322874=C2=A0=C2=A0=C2=A0=C2=A0 5484=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A00=C2=A0=C2=A0=C2=A0=C2=A0 0 - when done, uninstall: SELECT pgstat_snap.uninstall(); DROP SCHEMA cascade; The wait_event is a bit wonky for queries that are executed multiple=20 times per interval, it's the one the query was suffering from at the=20 time when the snapshot was taken. The script and full documentation is here:=20 https://github.com/raphideb/pgstat_snap Thought this might be useful for some, please let me know if there's=20 something I could improve. have fun raphi