RE: PostGIS performance issue
And the query? From: Shaozhong SHI Sent: Monday, March 18, 2024 7:57 AM To: Regina Obe Cc: PostGIS Users Discussion Subject: Re: PostGIS performance issue product_assurance=# SELECT version(), postgis_full_version(); version | postgis_full_version --+--- -- PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit | POSTGIS="3.1.10 4e93a1a" [EXTENSION] PGSQL="130" GEOS="3.12.1-CAPI-1.18.1" SFCGAL="1.4.1" PROJ="9.2.1" LIB XML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" On Thu, 22 Feb 2024 at 14:38, Regina Obe mailto:l...@pcorp.us> > wrote: Possible or one of the dependencies is different, or planner differences such as the CTE changes that came in PostgreSQL 12, but hard to tell given the information you have provided Or you didn’t reindex your data after the upgrade to a newer version of PostgreSQL and such a reindex was required. I have had to change some of my CTEs queries after upgrading to PostgreSQL 12+ to get back to same speed or better as prior runs for example. First of all? What does the below return now (and if you can remember the key bits like GEOS, PROJ, PostgreSQL Version what did you have before) SELECT version(), postgis_full_version(); Two, If you can provide the query you are trying to run, that would help, as we may see a pattern there. From: Shaozhong SHI mailto:shishaozh...@gmail.com> > Sent: Thursday, February 22, 2024 9:23 AM To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> > Subject: PostGIS performance issue My system has recently been upgraded from Linux 7 to Linux 8. There are performance issues with PostGIS "A process used to take 3.9 hours. Now, it has taken more than 2 days and yet it has not completed running. No errors have been flagged up." Is possible the PostGIS not configured properly? Regards, David
Re: PostGIS performance issue
product_assurance=# SELECT version(), postgis_full_version(); version | postgis_full_version --+--- -- PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit | POSTGIS="3.1.10 4e93a1a" [EXTENSION] PGSQL="130" GEOS="3.12.1-CAPI-1.18.1" SFCGAL="1.4.1" PROJ="9.2.1" LIB XML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" On Thu, 22 Feb 2024 at 14:38, Regina Obe wrote: > Possible or one of the dependencies is different, or planner differences > such as the CTE changes that came in PostgreSQL 12, but hard to tell given > the information you have provided > > Or you didn’t reindex your data after the upgrade to a newer version of > PostgreSQL and such a reindex was required. > > > > I have had to change some of my CTEs queries after upgrading to PostgreSQL > 12+ to get back to same speed or better as prior runs for example. > > > > First of all? > > > > What does the below return now (and if you can remember the key bits like > GEOS, PROJ, PostgreSQL Version what did you have before) > > > > SELECT version(), postgis_full_version(); > > > > > > Two, If you can provide the query you are trying to run, that would > help, as we may see a pattern there. > > > > > > > > *From:* Shaozhong SHI > *Sent:* Thursday, February 22, 2024 9:23 AM > *To:* PostGIS Users Discussion > *Subject:* PostGIS performance issue > > > > My system has recently been upgraded from Linux 7 to Linux 8. > > There are performance issues with PostGIS > > "A process used to take 3.9 hours. Now, it has taken more than 2 days and > yet it has not completed running. No errors have been flagged up." > > > > Is possible the PostGIS not configured properly? > > > > Regards, > > > > David >
RE: PostGIS performance issue
Possible or one of the dependencies is different, or planner differences such as the CTE changes that came in PostgreSQL 12, but hard to tell given the information you have provided Or you didn’t reindex your data after the upgrade to a newer version of PostgreSQL and such a reindex was required. I have had to change some of my CTEs queries after upgrading to PostgreSQL 12+ to get back to same speed or better as prior runs for example. First of all? What does the below return now (and if you can remember the key bits like GEOS, PROJ, PostgreSQL Version what did you have before) SELECT version(), postgis_full_version(); Two, If you can provide the query you are trying to run, that would help, as we may see a pattern there. From: Shaozhong SHI Sent: Thursday, February 22, 2024 9:23 AM To: PostGIS Users Discussion Subject: PostGIS performance issue My system has recently been upgraded from Linux 7 to Linux 8. There are performance issues with PostGIS "A process used to take 3.9 hours. Now, it has taken more than 2 days and yet it has not completed running. No errors have been flagged up." Is possible the PostGIS not configured properly? Regards, David
