From: | Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no> |
---|---|
To: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: ogr2ogr slow sql when checking system tables for column info and so on. |
Date: | 2022-10-21 10:30:27 |
Message-ID: | AM7P189MB1028138C916399DED4EB18A29D2D9@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
________________________________
From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
Sent: Friday, October 21, 2022 11:48 AM
To: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: ogr2ogr slow sql when checking system tables for column info and so on.
>From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
>Sent: Friday, October 21, 2022 11:48 AMTo: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>Cc: pgsql-performance(at)lists(dot)postgresql(dot)org <pgsql-performance(at)lists(dot)postgresql(dot)org>Subject: Re: ogr2ogr slow sql when checking system tables for column info and so on.
>
>Hi,
>
>> Here https://explain.depesz.com/s/oU19#stats the sql generated by ogr2ogr that takes 33 seconds in this sample
>> [...]
>> -> Seq Scan on pg_attribute a (rows=1464751) (actual time=0.028..17740.663
>> [...]
>> Then we take copy of the pg_catalog tables involved.
>>
>> Here https://explain.depesz.com/s/NEwB#source is the trace when using the same sql as from ogr2ogr but using the tables in test_pg_metadata and then it runs in 5 seconds.
>> [...]
>> -> Seq Scan on pg_attribute a (rows=1452385) (actual time=0.006..156.392
>>
>> I do not understand way it's so much slower to use the tables in pg_catalog than in test_pg_metadata tables because they have the same content.
>
>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.
Hi
Yes we use a lot of temp tables sometimes .
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.
I assume that adding more indexes on the tables in pg_catalog to avoid tables scans are not that easy.
Thanks for your help.
Lars
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2022-10-21 10:41:59 | Re: ogr2ogr slow sql when checking system tables for column info and so on. |
Previous Message | Julien Rouhaud | 2022-10-21 09:48:48 | Re: ogr2ogr slow sql when checking system tables for column info and so on. |