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 1uZzD4-00CIUn-BT for pgsql-admin@arkaria.postgresql.org; Thu, 10 Jul 2025 21:53:42 +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 1uZzD2-00HTm4-E3 for pgsql-admin@arkaria.postgresql.org; Thu, 10 Jul 2025 21:53:41 +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 1uZzD2-00HTlw-2L for pgsql-admin@lists.postgresql.org; Thu, 10 Jul 2025 21:53:40 +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 1uZzD0-006e7Z-0O for pgsql-admin@lists.postgresql.org; Thu, 10 Jul 2025 21:53:39 +0000 X-Spam-Status: No X-hosttech-MailScanner-From: raphi@crashdump.ch X-hosttech-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=-1.3, required 5, ALL_TRUSTED -1.00, BAYES_05 -0.50, HT_185 0.20) X-hosttech-MailScanner: Found to be clean X-hosttech-MailScanner-ID: 1654E3754036.A414E 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 1654E3754036 for ; Thu, 10 Jul 2025 23:53:14 +0200 (CEST) Message-ID: <0a6ce204-edf9-43da-8aa6-5c5ffc8183a5@crashdump.ch> Date: Thu, 10 Jul 2025 23:53:15 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pgstat_snap - create adhoc pg_stat_statements snapshots To: pgsql-admin@lists.postgresql.org References: <67c4e192-d928-481a-81cf-7080c2e79381@crashdump.ch> X-hosttech-server: 126.hosttech.eu From: raphi In-Reply-To: <67c4e192-d928-481a-81cf-7080c2e79381@crashdump.ch> 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, After some feedback I changed the script into an extension. Just copy=20 the files to the extension directory and load it with: create extension pgstat_snap; It can be installed in any schema and does not require super user=20 rights, only requirement is that the pg_stat_statement extension is loade= d. Usage is more or less the same, I've written a detailed description for=20 how to work with the extension on my github.io:=20 https://raphideb.github.io/postgres/pgstat_snap/#drill-down The extension replaced the script and is available here:=20 https://github.com/raphideb/pgstat_snap have fun ;) raphi Am 25.06.2025 um 16:55 schrieb raphi: > Hi all, > > I've created a script which helps me as a DBA to trace down=20 > performance problems, especially on clusters with multiple databases=20 > installed. It creates timestamped copies of pg_stat_statements and=20 > pg_stat_activity and provides two views that contain the execution=20 > delta of every query/dbid between timestamps, e.g. how many rows were=20 > affected. Basic 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=A0datname=C2= =A0=20 > usename wait_event_type wait_event=C2=A0=C2=A0=C2=A0=C2=A0 rows_d =C2=A0= calls_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=20 > pgbench_tell=C2=A0=C2=A0=C2=A0=C2=A0 postgres postgres=C2=A0=C2=A0=C2=A0= =C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 transactionid 4485=C2=A0=C2=A0=C2=A0=C2= =A0=20 > 4485=C2=A0=C2=A0=C2=A0=C2=A0 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=20 > pgbench_tell=C2=A0=C2=A0=C2=A0=C2=A0 postgres postgres=C2=A0=C2=A0=C2=A0= =C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 transactionid 1204=C2=A0=C2=A0=C2=A0=C2= =A0=20 > 1204=C2=A0=C2=A0=C2=A0=C2=A0 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=20 > pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0 postgres postgres=C2=A0=C2=A0=C2=A0= =C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 transactionid 1204=C2=A0=C2=A0=C2=A0=C2= =A0=20 > 1204=C2=A0=C2=A0=C2=A0=C2=A0 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=20 > pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0 postgres postgres=C2=A0=C2=A0=C2=A0= =C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 transactionid 1273=C2=A0=C2=A0=C2=A0=C2= =A0=20 > 1273=C2=A0=C2=A0=C2=A0=C2=A0 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=20 > pgbench_acco=C2=A0=C2=A0=C2=A0=C2=A0 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=20 > 9377=C2=A0=C2=A0=C2=A0=C2=A0 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=20 > pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0 postgres postgres=C2=A0=C2=A0=C2=A0= =C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 transactionid 1356=C2=A0=C2=A0=C2=A0=C2= =A0=20 > 1356=C2=A0=C2=A0=C2=A0=C2=A0 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=20 > pgbench_bran=C2=A0=C2=A0=C2=A0=C2=A0 postgres postgres=C2=A0=C2=A0=C2=A0= =C2=A0 Lock=C2=A0=C2=A0=C2=A0=C2=A0 transactionid 1168=C2=A0=C2=A0=C2=A0=C2= =A0=20 > 1168=C2=A0=C2=A0=C2=A0=C2=A0 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 > >