PostgreSQL 13 Explain now includes WAL information
The upcoming PostgreSQL 13 includes a lot of new features, as a very consolidated habit in every release. One interesting feature among the others is thatEXPLAIN now supports a new WAL option (that requires ANALYZE to be set).
This new
WAL feature allows EXPLAIN to provide information about the generated amount of WAL traffic.
It is quite simple to see it in action:
testdb=> CREATE TABLE foo( i int generated always as identity, t text );
testdb=> EXPLAIN ( ANALYZE, WAL, FORMAT yaml )
INSERT INTO foo( t )
SELECT md5( v::text )
FROM generate_series( 1, 300000 ) v;
QUERY PLAN
------------------------------------------
- Plan: +
Node Type: "ModifyTable" +
Operation: "Insert" +
Parallel Aware: false +
Relation Name: "foo" +
Alias: "foo" +
Startup Cost: 0.00 +
Total Cost: 6000.00 +
Plan Rows: 300000 +
Plan Width: 36 +
Actual Startup Time: 508.168 +
Actual Total Time: 508.168 +
Actual Rows: 0 +
Actual Loops: 1 +
WAL Records: 309091 +
WAL FPI: 0 +
WAL Bytes: 28500009 +
...
As you can see, the output of
EXPLAIN now includes three new nodes:
WAL Records, as the name suggests, is the number of WAL records inserted into the logs;WAL FPIis the number of the Full Page Images inserted into the WALs;WAL bytesis the amount of traffic generated towards the WAL logs.
```sql testdb=> EXPLAIN ( ANALYZE, WAL, FORMAT yaml ) INSERT INTO foo( t ) SELECT md5( v::text ) FROM generate_series( 1, 3 ) v;
QUERY PLAN ------------------------------------------ - Plan: +
Node Type: "ModifyTable" +
Operation: "Insert" +
...
WAL Records: 3 +
WAL FPI: 0 +
WAL Bytes: 276 +
... ``** <br/> <br/>
auto_explain does support WAL information dump too, via the special configuration parameter auto_explain.log_wal.