|
>From: Justin Pryzby <pryzby@xxxxxxxxxxxxx>Sent: Wednesday, March 23, 2022 2:19 PMTo: Lars Aksel Opsahl <Lars.Opsahl@xxxxxxxx>Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>Subject: Re: Using system tables directly takes
many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.
>
>On Wed, Mar 23, 2022 at 09:44:09AM +0000, Lars Aksel Opsahl wrote:
>> Why is temp tables with no indexes much faster system tables with indexes ?
>
>I think the "temp table" way is accidentally faster due to having no
>statistics, not because it has no indexes. If you run ANALYZE, you may hit the
>same issue (or, maybe you just need to VACUUM ANALYZE your system catalogs).
Hi
I had tested this in the morning and it did not work (VACUUM ANALYZE pg_class; VACUUM ANALYZE pg_attribute; VACUUM ANALYZE pg_namespace; VACUUM ANALYZE raster_columns; VACUUM ANALYZE pg_type; )
But now it seemed to work maybe one time, the 50 secs query
(https://explain.depesz.com/s/Op7i)
was down to 6 secs, but just to be sure I rerun the query one more time and we where where back to execution time of 50 seconds.
It seems like stats may be valid for just some few seconds before having to run analyze again and that takes a long time.
The 45 jobs running on the server are creating a lot temp tables and maybe some unlogged tables
We can not run run analyze in every job because this may be many hundred thounsed jobs that we need to run.
Does this mean that we can not use temp tables in this extent and in stead use
https://www.postgresql.org/docs/12/queries-with.html ?
But the problem with "with" is that we can not create indexes.Or is a option to exclude temp tables geometry_columns in effective way , but that will probably cause problems if we create temp table in jobs where we use postgis.so that not a good solution
either,
Thanks
Lars
|
