Date: Fri, 21 Oct 2022 18:41:59 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: ogr2ogr slow sql when checking system tables for column info and
 so on.
Message-ID: <20221021104159.ijdnpknv52fweaxk@jrouhaud>
References: 
 <AM7P189MB1028C24285B30353A16DD9739D2D9@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
 <20221021094848.5gbp42wttkjzn3le@jrouhaud>
 <AM7P189MB1028138C916399DED4EB18A29D2D9@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <AM7P189MB1028138C916399DED4EB18A29D2D9@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
Archived-At: 
 <https://www.postgresql.org/message-id/20221021104159.ijdnpknv52fweaxk%40jrouhaud>
Precedence: bulk

Hi,

On Fri, Oct 21, 2022 at 10:30:27AM +0000, Lars Aksel Opsahl wrote:
>
> >
> >In both case you have a sequential scan over the pg_attribute table, but for
> >pg_catalog it takes 17 seconds to retrieve the 1.4M rows, and in the new table
> >it takes 156 ms.
> >
> >It looks like you catalog is heavily bloated, which is the cause of the
> >slowdown.
> >
> >You could do a VACUUM FULL of the tables in pg_catalog but it would only be a
> >short term fix as it's likely that your catalog will get bloated again.  Do you
> >rely a lot on temporary tables?  If yes it can easily lead to this kind of side
> >effect, and you should modify you code to perform manual vacuum of catalogs
> >tables very often, or add a dedicated high frequency task for running similar
> >vacuum and keep the bloat under control.
>
> Yes we use a lot of temp tables sometimes .

What do you mean by sometimes?  If you only have non frequent or specialized
jobs the creates a lot of temp tables, you just need to modify them to issue
some VACUUM (not VACUUM FULL) at the end, or regularly if you creates millions
of tables in a single job.

> With "VACUUM FULL ANALYZE " we got the same time as from the created tables
> https://explain.depesz.com/s/Yxy9 so that works.
>
> OK then we start up by trigger a 'VACUUM FULL ANALYZE ' for all the tables in
> th pg_catalog because this seems to be only thing that is working for now.

Just to be clear the VACUUM FULL is only needed to shrink the tables which were
likely multiple GB each.  If you do simple VACUUM frequently enough, you won't
have too much bloat in the first place and everything will just work as
intended.

You could setup some monitoring on the size of the catalog tables to make sure
that you run those maintenance as frequently as necessary.

> I assume that adding more indexes on the tables in pg_catalog to avoid tables
> scans are not  that easy.

Indeed, that's not a supported operation.