Re: High process memory consumption when running sort

2022-03-23 Thread Justin Pryzby
On Wed, Mar 23, 2022 at 02:42:06PM +, Shai Shapira wrote: > Hi, > > When running our application, we noticed that some processes are taking a lot > of memory ( 10, 15, 20GB or so, of RSS ). > It is also reproduced when running in psql. Note that RSS can include shared_buffers read by that ba

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Lars Aksel Opsahl
>From: Justin Pryzby Sent: Wednesday, March 23, 2022 2:19 >PMTo: Lars Aksel Opsahl Cc: >pgsql-performance@lists.postgresql.org >Subject: Re: Using system tables >directly takes many hours, using temp tables with no indexes takes a few >seconds for geometry_co

High process memory consumption when running sort

2022-03-23 Thread Shai Shapira
Hi, When running our application, we noticed that some processes are taking a lot of memory ( 10, 15, 20GB or so, of RSS ). It is also reproduced when running in psql. PG version is 12.6 2 examples: * Common table, PG_BUFFERCACHE, when doing group by, session takes 140MB, which is not a l

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Justin Pryzby
On Wed, Mar 23, 2022 at 09:44:09AM +, 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

Re: Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Lars Aksel Opsahl
>Hi > >We are running >postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)) >POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" >SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29" >LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

2022-03-23 Thread Lars Aksel Opsahl
Hi We are running postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)) POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLO