Re: Very slow "bloat query"

There is a long running analytics query (which is running usually for 30-40
hours). I agree that's not the best position to be in but right now can't
do anything about it.

pt., 14 maj 2021 o 15:04 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napisał(a):

> Marcin Gozdalik <gozdal(at)gmail(dot)com> writes:
> > I have traced the problem to the bloated `pg_class` (the irony:
> `pgmetrics`
> > does not collect bloat on `pg_catalog`):
> > `vacuum (full, analyze, verbose) pg_class;`
> > ```
> > INFO: vacuuming "pg_catalog.pg_class"
> > INFO: "pg_class": found 1 removable, 7430805 nonremovable row versions
> in
> > 158870 pages
> > DETAIL: 7429943 dead row versions cannot be removed yet.
>
> Ugh. It's understandable that having a lot of temp-table traffic
> would result in the creation of lots of dead rows in pg_class.
> The question to be asking is why aren't they vacuumable? You
> must have a longstanding open transaction somewhere (perhaps
> a forgotten prepared transaction?) that is holding back the
> global xmin horizon. Closing that out and then doing another
> manual VACUUM FULL should help.
>
> regards, tom lane
>

--
Marcin Gozdalik