From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: [SOLVED?] Re: Disk wait problem... not hardware... |
Date: | 2023-10-30 09:53:38 |
Message-ID: | 20231030095338.ie3b55gcqzo7lr22@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2023-10-29 12:45:08 -0400, pf(at)pfortin(dot)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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2023-10-30 10:01:13 | Re: pg_checksums? |
Previous Message | Justin Clift | 2023-10-30 06:29:08 | Re: Server process exited with exit code 4 |