Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics

On 11st of February 2023, Andres Freund committed patch:

Add pg_stat_io view, providing more detailed IO statistics
 
Builds on 28e626bde00 and f30d62c2fc6. See the former for motivation.
 
Rows of the view show IO operations for a particular backend type, IO target
object, IO context combination (e.g. a client backend's operations on
permanent relations in shared buffers) and each column in the view is the
total number of IO Operations done (e.g. writes). So a cell in the view would
be, for example, the number of blocks of relation data written from shared
buffers by client backends since the last stats reset.
 
In anticipation of tracking WAL IO and non-block-oriented IO (such as
temporary file IO), the "op_bytes" column specifies the unit of the "reads",
"writes", and "extends" columns for a given row.
 
Rows for combinations of IO operation, backend type, target object and context
that never occur, are ommitted entirely. For example, checkpointer will never
operate on temporary relations.
 
Similarly, if an IO operation never occurs for such a combination, the IO
operation's cell will be null, to distinguish from 0 observed IO
operations. For example, bgwriter should not perform reads.
 
Note that some of the cells in the view are redundant with fields in
pg_stat_bgwriter (e.g. buffers_backend). For now, these have been kept for
backwards compatibility.
 
Bumps catversion.
 
Author: Melanie Plageman <melanieplageman@gmail.com>
Author: Samay Sharma <smilingsamay@gmail.com>
Reviewed-by: Maciek Sakrejda <m.sakrejda@gmail.com>
Reviewed-by: Lukas Fittl <lukas@fittl.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Discussion: https://postgr.es/m/20200124195226.lth52iydq2n2uilq@alap3.anarazel.de

Continue reading Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics

Why is it hard to automatically suggest what index to create?

Every now and then someone asks me to add index suggestions to explain.depesz.com.

I always respond with polite decline. This is complicated thing to do, and I just don't have that time.

Lately I was asked, on Slack to add (to explain.depesz.com) link to pganalyze Index Advisor for Postgres.

So I checked it out. And results prompted me to write this blogpost.

Continue reading Why is it hard to automatically suggest what index to create?

Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time

On 17th of January 2021, Magnus Hagander committed patch:

Add pg_stat_database counters for sessions and session time
 
This add counters for number of sessions, the different kind of session
termination types, and timers for how much time is spent in active vs
idle in a database to pg_stat_database.
 
Internally this also renames the parameter "force" to disconnect. This
was the only use-case for the parameter before, so repurposing it to
this mroe narrow usecase makes things cleaner than inventing something
new.
 
Author: Laurenz Albe
Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda
Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at

Continue reading Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time

Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics

On 11st of September 2020, Alvaro Herrera committed patch:

psql: Display stats target of extended statistics
 
The stats target can be set since commit d06215d03, but wasn't shown by
psql.
 
Author: Justin Pryzby <justin@telsasoft.com>
Discussion: https://postgr.es/m/20200831050047.GG5450@telsasoft.com
Reviewed-by: Georgios Kokolatos <gkokolatos@protonmail.com>
Reviewed-by: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>

Continue reading Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics

Waiting for PostgreSQL 10 – Implement multivariate n-distinct coefficients

I missed it completely, but on 24th of March 2017, Alvaro Herrera committed patch:

Implement multivariate n-distinct coefficients
 
 
Add support for explicitly declared statistic objects (CREATE
STATISTICS), allowing collection of statistics on more complex
combinations that individual table columns.  Companion commands DROP
STATISTICS and ALTER STATISTICS ... OWNER TO / SET SCHEMA / RENAME are
added too.  All this DDL has been designed so that more statistic types
can be added later on, such as multivariate most-common-values and
multivariate histograms between columns of a single table, leaving room
for permitting columns on multiple tables, too, as well as expressions.
 
This commit only adds support for collection of n-distinct coefficient
on user-specified sets of columns in a single table.  This is useful to
estimate number of distinct groups in GROUP BY and DISTINCT clauses;
estimation errors there can cause over-allocation of memory in hashed
aggregates, for instance, so it's a worthwhile problem to solve.  A new
special pseudo-type pg_ndistinct is used.
 
(num-distinct estimation was deemed sufficiently useful by itself that
this is worthwhile even if no further statistic types are added
immediately; so much so that another version of essentially the same
functionality was submitted by Kyotaro Horiguchi:
https://postgr.es/m/.173334..horiguchi.kyotaro@lab.ntt.co.jp
though this commit does not use that code.)
 
Author: Tomas Vondra.  Some code rework by Álvaro.
 
    Ideriha Takeshi
Discussion: https://postgr.es/m/.4080608@fuzzy.cz
    https://postgr.es/m/.ixlaueanxegqd5gr@alvherre.pgsql

Afterwards, there were couple more commits related to it:

  • On 5th of April 2017, patch committed by Simon Riggs
  • On 17th of April 2017, patch committed by Alvaro Herrera
  • On 12nd of May 2017, patch committed by Alvaro Herrera

Continue reading Waiting for PostgreSQL 10 – Implement multivariate n-distinct coefficients

Waiting for 9.5 – Add stats for min, max, mean, stddev times to pg_stat_statements.

On 27th of March, Andrew Dunstan committed patch:

Add stats for min, max, mean, stddev times to pg_stat_statements.
 
The new fields are min_time, max_time, mean_time and stddev_time.
 
Based on an original patch from Mitsumasa KONDO, modified by me. Reviewed by Petr Jelínek.

Continue reading Waiting for 9.5 – Add stats for min, max, mean, stddev times to pg_stat_statements.

Waiting for 9.4 – Add pg_stat_archiver statistics view.

On 28th of January, Fujii Masao committed patch:

Add pg_stat_archiver statistics view.
 
This view shows the statistics about the WAL archiver process's activity.
 
Gabriele Bartolini, reviewed by Michael Paquier, refactored a bit by me.

Continue reading Waiting for 9.4 – Add pg_stat_archiver statistics view.

Explaining the unexplainable – part 5

In previous posts in this series, I talked about how to read EXPLAIN output, and what each line (operation/node) means.

Now, in the final post, I will try to explain how it happens that Pg chooses “Operation X" over “Operation Y".

Continue reading Explaining the unexplainable – part 5

Waiting for 9.2 – temporary file stats per database

On 26th of January, Magnus Hagander committed patch:

Add counters for number and size of temporary files used
for spill-to-disk queries for each database to the
pg_stat_database view.
 
Tomas Vondra, review by Magnus Hagander

Continue reading Waiting for 9.2 – temporary file stats per database