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.
Date: Fri, 21 Oct 2022 18:41:59 +0800
Message-ID: <20221021104159.ijdnpknv52fweaxk@jrouhaud> (raw)
In-Reply-To: <AM7P189MB1028138C916399DED4EB18A29D2D9@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
References: <AM7P189MB1028C24285B30353A16DD9739D2D9@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
<20221021094848.5gbp42wttkjzn3le@jrouhaud>
<AM7P189MB1028138C916399DED4EB18A29D2D9@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rjuju123@gmail.com, Lars.Opsahl@nibio.no, pgsql-performance@lists.postgresql.org
Subject: Re: ogr2ogr slow sql when checking system tables for column info and so on.
In-Reply-To: <20221021104159.ijdnpknv52fweaxk@jrouhaud>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox