[postgis-users] Configuration and performance of PostGIS

[James Klassen]

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.

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220421/13d1ca42/attachment.html>