Re: [SOLVED?] Re: Disk wait problem... not hardware... - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: [SOLVED?] Re: Disk wait problem... not hardware...
Date
Msg-id 20231030095338.ie3b55gcqzo7lr22@hjp.at
Whole thread Raw
In response to Re: [SOLVED?] Re: Disk wait problem... not hardware...  (pf@pfortin.com)
List pgsql-general
On 2023-10-29 12:45:08 -0400, pf@pfortin.com wrote:
> On Sun, 29 Oct 2023 16:16:05 +0100 Peter J. Holzer wrote:
> >However, the table statistics contain an estimate for the number of
> >rows:
> >
> >hjp=> select schemaname, relname, n_live_tup from pg_stat_user_tables order by 3 desc;
> >╔════════════╤═══════════════════════════╤════════════╗
> >║ schemaname │          relname          │ n_live_tup ║
> >╟────────────┼───────────────────────────┼────────────╢
> >║ public     │ ncvhis_2016_12_03         │    9999977 ║
> >║ public     │ random_test               │    1000000 ║
> >║ public     │ beislindex                │        351 ║
> >║ public     │ delivery                  │          6 ║
> >...
>
> Are there prerequisites for this query?
> Every (264) n_live_tup returned was zero...  though, VERY fast.. :)

You need to ANALYZE the tables. Autovacuum should do this automatically
when it detects that a table has changed "enough" since the last
analyze, but for slowly growing tables that can sometimes take a long
time (indeed, I did an "analyze beislindex" just before the query above
because it showed only 25 tuples and I knew that wasn't right).

A count of 0 for a large table is weird, though.
Unless ... did you do a major version upgrade recently? That nukes the
statistics and you have to analyze everything again. Also, I'm not sure
if pg_restore triggers an analyze.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: Justin Clift
Date:
Subject: Re: Server process exited with exit code 4
Next
From: "Peter J. Holzer"
Date:
Subject: Re: pg_checksums?