On Wed, Nov 18, 2026 at 12:21 PM Lukas Fittl <[email protected]> wrote: > > On Wed, Nov 18, 2026 at 8:06 AM Melanie Plageman > <[email protected]> wrote: > > > I've committed the fix and backpatched it. The question is whether we > > want master to return NULL when there was not enough information to > > make an estimate for approx_tuple_count. > > Personally I still think returning NULL is the better choice on master > - its easy enough to update for a client program, and I think 0 is > ambiguous (so we should try to get away from that ambiguity over > time).
Cool. I'll commit the attached tomorrow then. - Melanie
From 7bc7cda75d8908b56a745661df11d915dd2f8c52 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <[email protected]> Date: Wed, 18 Nov 2026 14:05:28 -0500 Subject: [PATCH v1] Return NULL for unknown pgstattuple estimate pgstattuple_approx() returns 0 when there is not enough information to estimate the number of live tuples, making an unknown estimate indistinguishable from an estimate of an empty table. Return NULL for approx_tuple_count instead --- contrib/pgstattuple/expected/pgstattuple.out | 4 ++-- contrib/pgstattuple/pgstatapprox.c | 10 +++++++--- contrib/pgstattuple/sql/pgstattuple.sql | 2 +- doc/src/sgml/pgstattuple.sgml | 5 +++-- 4 files changed, 13 insertions(+), 8 deletions(-) diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out index 74813d0c751..f7f0967d6fe 100644 --- a/contrib/pgstattuple/expected/pgstattuple.out +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -250,7 +250,7 @@ select pg_relpages((select reltoastrelid from pg_class where relname = 'test')); 0 (1 row) --- An unknown approximate tuple count is reported as zero. +-- An unknown approximate tuple count is reported as NULL. begin; create temp table test_approx_unknown (id int); copy test_approx_unknown from stdin with (freeze); @@ -261,7 +261,7 @@ select scanned_percent, approx_tuple_count from pgstattuple_approx('test_approx_unknown'); scanned_percent | approx_tuple_count -----------------+-------------------- - 0 | 0 + 0 | (1 row) commit; diff --git a/contrib/pgstattuple/pgstatapprox.c b/contrib/pgstattuple/pgstatapprox.c index 8fd5e8d877e..85d29d6a126 100644 --- a/contrib/pgstattuple/pgstatapprox.c +++ b/contrib/pgstattuple/pgstatapprox.c @@ -36,6 +36,7 @@ typedef struct output_type uint64 table_len; double scanned_percent; uint64 tuple_count; + bool tuple_count_is_unknown; uint64 tuple_len; double tuple_percent; uint64 dead_tuple_count; @@ -259,8 +260,10 @@ statapprox_heap(Relation rel, output_type *stat) p.scanned_new, stat->tuple_count); - /* Report zero when there is not enough information for an estimate. */ - stat->tuple_count = Max(estimated_tuple_count, 0); + if (estimated_tuple_count < 0) + stat->tuple_count_is_unknown = true; + else + stat->tuple_count = estimated_tuple_count; /* * Calculate percentages if the relation has one or more pages. @@ -365,7 +368,8 @@ pgstattuple_approx_internal(Oid relid, FunctionCallInfo fcinfo) values[i++] = Int64GetDatum(stat.table_len); values[i++] = Float8GetDatum(stat.scanned_percent); - values[i++] = Int64GetDatum(stat.tuple_count); + values[i] = Int64GetDatum(stat.tuple_count); + nulls[i++] = stat.tuple_count_is_unknown; values[i++] = Int64GetDatum(stat.tuple_len); values[i++] = Float8GetDatum(stat.tuple_percent); values[i++] = Int64GetDatum(stat.dead_tuple_count); diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql index 9c543e68d94..52682ec2fa6 100644 --- a/contrib/pgstattuple/sql/pgstattuple.sql +++ b/contrib/pgstattuple/sql/pgstattuple.sql @@ -107,7 +107,7 @@ select pgstattuple((select reltoastrelid from pg_class where relname = 'test')); select pgstattuple_approx((select reltoastrelid from pg_class where relname = 'test')); select pg_relpages((select reltoastrelid from pg_class where relname = 'test')); --- An unknown approximate tuple count is reported as zero. +-- An unknown approximate tuple count is reported as NULL. begin; create temp table test_approx_unknown (id int); copy test_approx_unknown from stdin with (freeze); diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml index 8828f4f1272..4fa1e9d43d1 100644 --- a/doc/src/sgml/pgstattuple.sgml +++ b/doc/src/sgml/pgstattuple.sgml @@ -572,8 +572,9 @@ approx_free_percent | 2.09 <row> <entry><structfield>approx_tuple_count</structfield></entry> <entry><type>bigint</type></entry> - <entry>Number of live tuples (estimated), or zero if there is not - enough information to estimate it</entry> + <entry>Number of live tuples (estimated), or + <literal>NULL</literal> if there is not enough information to estimate + it</entry> </row> <row> <entry><structfield>approx_tuple_len</structfield></entry> -- 2.43.0
