[postgis-users] Configuration and performance of PostGIS

[Shaozhong SHI]

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?

Regards,

David



>
> 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>