Author: depesz

Changes on explain.depesz.com

Just pushed some new changes to expain.depesz.com and it's underlying parsing library.

The changes are:

  • If you upload plan in JSON/YAML/XML format, and then you'll look at “TEXT" tab, you will see explain in text format, generated using data from JSON. This explain had slight error in number formatting (3 decimal digits for estimated costs, while text formats use only 2 decimals). THis was reported by ysch on irc. Example plan
  • Numbers in node details (for example: Filtered, or Buffers info) are now commified to make it easier to read. Example plan
  • Fixed parsing of Planning time in JSON, YAML, and XML formats of explains from PostgreSQL 13. Example plans:
    JSON, YAML, and XML.

Some stats, for curious ones:

  • site is up for over 11 years and 6 months
  • so far there have been 843,215 plans added
  • within last 30 days, there have been 484 new plans added daily, on average
  • there are 1295 registered users, but only 780 of them ever added any plans

Waiting for PostgreSQL 13 – Add logical replication support to replicate into partitioned tables

On 6th of April 2020, Peter Eisentraut committed patch:

Add logical replication support to replicate into partitioned tables 
 
Mainly, this adds support code in logical/worker.c for applying
replicated operations whose target is a partitioned table to its
relevant partitions.
 
Author: Amit Langote <amitlangote09@gmail.com>
Reviewed-by: Rafia Sabih <rafia.pghackers@gmail.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Reviewed-by: Petr Jelinek <petr@2ndquadrant.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add logical replication support to replicate into partitioned tables

Waiting for PostgreSQL 13 – Allow autovacuum to log WAL usage statistics.

On 6th of April 2020, Amit Kapila committed patch:

Allow autovacuum to log WAL usage statistics. 
 
This commit allows autovacuum to log WAL usage statistics added by commit
df3b181499.
 
Author: Julien Rouhaud
Reviewed-by: Dilip Kumar and Amit Kapila
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Allow autovacuum to log WAL usage statistics.

Waiting for PostgreSQL 13 – Add the option to report WAL usage in EXPLAIN and auto_explain.

On 6th of April 2020, Amit Kapila committed patch:

Add the option to report WAL usage in EXPLAIN and auto_explain.
 
This commit adds a new option WAL similar to existing option BUFFERS in the
EXPLAIN command.  This option allows to include information on WAL record
generation added by commit df3b181499 in EXPLAIN output.
 
This also allows the WAL usage information to be displayed via
the auto_explain module.  A new parameter auto_explain.log_wal controls
whether WAL usage statistics are printed when an execution plan is logged.
This parameter has no effect unless auto_explain.log_analyze is enabled.
 
Author: Julien Rouhaud
Reviewed-by: Dilip Kumar and Amit Kapila
Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add the option to report WAL usage in EXPLAIN and auto_explain.

Changes on explain.depesz.com

I just released new version of Pg::Explain Perl library that is handling parsing of plans for explain.depesz.com.

There are quite a lot of changes, but mostly internal, but one thing is pretty interesting – Pg::Explain, and because of this also explain.depesz.com should be able to parse plans with arbitrary values of border, linestyle, format, unicode_border_linestyle, unicode_column_linestyle, and unicode_header_linestyle psql options.

You can see five simple examples already uploaded:
Continue reading Changes on explain.depesz.com

Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

On 2nd of April 2020, Fujii Masao committed patch:

Allow pg_stat_statements to track planning statistics.
 
This commit makes pg_stat_statements support new GUC
pg_stat_statements.track_planning. If this option is enabled,
pg_stat_statements tracks the planning statistics of the statements,
e.g., the number of times the statement was planned, the total time
spent planning the statement, etc. This feature is useful to check
the statements that it takes a long time to plan. Previously since
pg_stat_statements tracked only the execution statistics, we could
not use that for the purpose.
 
The planning and execution statistics are stored at the end of
each phase separately. So there are not always one-to-one relationship
between them. For example, if the statement is successfully planned
but fails in the execution phase, only its planning statistics are stored.
This may cause the users to be able to see different pg_stat_statements
results from the previous version. To avoid this,
pg_stat_statements.track_planning needs to be disabled.
 
This commit bumps the version of pg_stat_statements to 1.8
since it changes the definition of pg_stat_statements function.
 
Author: Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao
Reviewed-by: Sergei Kornilov, Tomas Vondra, Yoshikazu Imai, Haribabu Kommi, Tom Lane
Discussion: https://postgr.es/m/CAHGQGwFx_=DO-Gu-MfPW3VQ4qC7TfVdH2zHmvZfrGv6fQ3D-Tw@mail.gmail.com
Discussion: https://postgr.es/m/CAEepm=0e59Y_6Q_YXYCTHZkqOc6H2pJ54C_Xe=VFu50Aqqp_sA@mail.gmail.com
Discussion: https://postgr.es/m/DB6PR0301MB21352F6210E3B11934B0DCC790B00@DB6PR0301MB2135.eurprd03.prod.outlook.com

Continue reading Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

On 18th of March 2020, Alvaro Herrera committed patch:

Enable BEFORE row-level triggers for partitioned tables
 
... with the limitation that the tuple must remain in the same
partition.
 
Reviewed-by: Ashutosh Bapat
Discussion: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql

Continue reading Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

Initial support for fixing of line-wrapped plans

Ever since I released explain.depesz.com over 11 years ago there have been cases were people would upload a plan and it didn't parse.

There were many reasons, but the most common was – plan was line-wrapped by injecting new-line characters where there shouldn't be one.

Continue reading Initial support for fixing of line-wrapped plans

Fix for displaying aggregates on explain.depesz.com

Couple of days ago RhodiumToad reported, on irc, a bug in explain.depesz.com.

Specifically – if explain was done using JSON/XML/YAML formats, and node type was Aggregate, the site didn't extract full info.

Continue reading Fix for displaying aggregates on explain.depesz.com