Show a human-readable n_distinct in pg_stats view
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t40270psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 20, 2026 at 12:42 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t40270_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t40270_1 && git checkout t40270_1Patchset v1 (message #1) is on t40270_1
Hi,
It seems to me that since the pg_stats view is supposed to be
human-readable, it would make sense to show a human-readable version
of n_distinct.
Currently, when the stats collector estimates that the number of
distinct values is more than 10% of the total row count, what is
stored in pg_statistic.stadistinct is -1 * n_distinct / totalrows, the
rationale being that if new rows are inserted in the table, they are
likely to introduce new values, and storing that value allows the
stadistinct not to get stale too fast.
You can find attached a simple WIP patch to show the proper n_distinct
value in pg_stats.
* Is this desired?
* Would it make sense to add a column in the pg_stats view to display
the information "lost", that is the fact that postgres will assume
that inserting new rows means a higher n_distinct?
* Am I right to assume that totalrows in the code
(src/backend/commands/analyze.c:2170) actually corresponds to
n_live_tup? That's what I gathered from glancing at the code, but I
might be wrong.
* Should the catalog version be changed for this kind of change?
* Should I add this patch to the commitfest?
If this patch is actually desired, I'll update the documentation as well.
I'm guessing this patch would break scripts relying on the pg_stats
view, but I do not know how much we want to avoid that, since they
should rely on the base tables rather than on the views.
Thanks in advance for your input!
Regards,
Maxence Ahlouche
On Wed, 13 Mar 2019 at 15:14, Maxence Ahlouche
<maxence.ahlouche@gmail.com> wrote:
* Should I add this patch to the commitfest?
I added it: https://commitfest.postgresql.org/23/2061/
Maxence Ahlouche wrote:
It seems to me that since the pg_stats view is supposed to be
human-readable, it would make sense to show a human-readable version
of n_distinct.
Currently, when the stats collector estimates that the number of
distinct values is more than 10% of the total row count, what is
stored in pg_statistic.stadistinct is -1 * n_distinct / totalrows, the
rationale being that if new rows are inserted in the table, they are
likely to introduce new values, and storing that value allows the
stadistinct not to get stale too fast.You can find attached a simple WIP patch to show the proper n_distinct
value in pg_stats.* Is this desired?
* Would it make sense to add a column in the pg_stats view to display
the information "lost", that is the fact that postgres will assume
that inserting new rows means a higher n_distinct?
* Am I right to assume that totalrows in the code
(src/backend/commands/analyze.c:2170) actually corresponds to
n_live_tup? That's what I gathered from glancing at the code, but I
might be wrong.
* Should the catalog version be changed for this kind of change?
* Should I add this patch to the commitfest?If this patch is actually desired, I'll update the documentation as well.
I'm guessing this patch would break scripts relying on the pg_stats
view, but I do not know how much we want to avoid that, since they
should rely on the base tables rather than on the views.
This may make things easier for those who are confused by a negative
entry, but it will obfuscate matters for those who are not.
I don't think that is a win, particularly since the semantics are
explained in great detail in the documentation of "pg_stats".
So I am -1 on that one.
Yours,
Laurenz Albe