Date: Tue, 24 Jan 2023 14:51:55 +0100 (CET)
From: Erik Wienhold <ewie@ewie.name>
To: Loles <lolesft@gmail.com>, pgsql-admin@postgresql.org
Message-ID: <2014960602.121600.1674568315040@office.mailbox.org>
In-Reply-To: 
 <CA+FWGK6U4RfPZqE=1RMTwU8ezqhJp8NBGuf_pErLF-RbtJ_FNA@mail.gmail.com>
References: 
 <CA+FWGK6U4RfPZqE=1RMTwU8ezqhJp8NBGuf_pErLF-RbtJ_FNA@mail.gmail.com>
Subject: Re: No problem, only curiosity
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/2014960602.121600.1674568315040%40office.mailbox.org>
Precedence: bulk

> On 24/01/2023 14:06 CET Loles <lolesft@gmail.com> wrote:
>
> Does anyone know why I am getting a result less than 2 million rows in th=
is
> example?
>
> postgres=3D# create table test_n_tup_live (id serial);
> CREATE TABLE
>
> postgres=3D# insert into test_n_tup_live select generate_series(1,1000000=
);
> INSERT 0 1000000
>
> postgres=3D# analyze test_n_tup_live ;
> ANALYZE
>
> postgres=3D# select n_live_tup, n_dead_tup from pg_stat_user_tables where=
 relname =3D 'test_n_tup_live';
>
> =C2=A0n_live_tup | n_dead_tup
> ------------+------------
>  1000000 | 0
> (1 fila)
>
> postgres=3D# update test_n_tup_live set id =3D id + 1;
> UPDATE 1000000
>
> postgres=3D# insert into test_n_tup_live select generate_series(1,1000000=
);
> INSERT 0 1000000
>
> postgres=3D# select n_live_tup, n_dead_tup from pg_stat_user_tables where=
 relname =3D 'test_n_tup_live';
> =C2=A0n_live_tup | n_dead_tup
> ------------+------------
>  2000000 | 1000000
> (1 fila)
>
> postgres=3D# vacuum test_n_tup_live;
> VACUUM
>
> postgres=3D# select n_live_tup, n_dead_tup from pg_stat_user_tables where=
 relname =3D 'test_n_tup_live';
>
> =C2=A0n_live_tup | n_dead_tup
> ------------+------------
>  1999925 | 0
> (1 fila)

Both numbers are estimates: https://www.postgresql.org/docs/current/monitor=
ing-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW

--
Erik