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

Waiting for 9.1 – stats reset tracking

On 10th of February, Magnus Hagander committed patch:

Track last time for statistics reset on databases and bgwriter
 
Tracks one counter for each database, which is reset whenever
the statistics for any individual object inside the database is
reset, and one counter for the background writer.
 
Tomas Vondra, reviewed by Greg Smith

Continue reading Waiting for 9.1 – stats reset tracking

Waiting for 8.5 – hinting for number of distinct values

Robert Haas wrote, and Tom Lane committed (on 2nd of August) patch which adds interesting capability:

Log Message:
-----------
ADD ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT
 
Robert Haas

Log message is rather terse, so what does it exactly do?

Continue reading Waiting for 8.5 – hinting for number of distinct values