[postgis-users] Configuration and performance of PostGIS

Shaozhong SHI shishaozhong at gmail.com
Fri Apr 22 01:03:33 PDT 2022

On Fri, 22 Apr 2022 at 05:05, James Klassen <klassen.js at gmail.com> wrote:

> Use EXPLAIN to make sure your query is using indexes appropriately.  And
> make sure you have created the appropriate indexes.
> Make sure you have the various PostgreSQL memory settings configured
> appropriately.  I’ve seen cases where increasing the memory available to
> PostgreSQL from the default being the difference between a query taking
> over 24 hours with the default settings to the same query taking less that
> 5 minutes.  It is very slow if PostgreSQL needs to use a temporary disk
> file because it doesn’t have enough RAM to do things like sorts and joins
> in memory.
> There are many examples on the web of how to use explain and how to
> optimize the memory settings for your computer and usage.

Would indexing make any difference on that recursive query?



> On Thu, Apr 21, 2022 at 18:21 Shaozhong SHI <shishaozhong at gmail.com>
> wrote:
>> Whenever geospatial functions such as St_intersects or recursive query
>> used, the PostGIS appears to spawn away to many child queries and just
>> obliterate the CPU.  Nothing finishes.
>> That forced me to try out to do the some tasks on the FME server.
>> I tried to use this http://blog.cleverelephant.ca/2010/07/network
>> -walking-in-postgis.html in the PostGIS.
>> I tried to linecombiner in FME.  LineCombiner | FME (safe.com)
>> <https://www.safe.com/transformers/line-combiner/>.
>> With a large data set, the running of processors were monitored.  It was
>> estimated the PostGIS one would take 16 days to complete.
>> But, it only took a few minute to do the same thing in FME.
>> This suggests that something is not right with the PostGIS Server.
>> Have anyone got experience with configuration and improving perfomance of
>> PostGIS Server?
>> Regards,
>> David
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220422/d2ef1b83/attachment.html>

More information about the postgis-users mailing list