[postgis-users] Issues of slow running queries when dealing with Big Data

[Imre Samu]

> What are the best approaches and means for improving the performance of
queries and processes in PostgreSQL/PostGIS?

imho:  no silver bullet
just some general tips + experience + practice(~learning with pain)
- https://postgis.net/docs/performance_tips.html
- https://www.postgresql.org/docs/12/performance-tips.html
- https://wiki.postgresql.org/wiki/Performance_Optimization
- https://wiki.postgresql.org/wiki/Slow_Query_Questions

my favorite first line tricks:
- slim/optimal data model  ( column types )
- materialised views
- partitioning !
- indexing
- processing/grouping  data by  Geohash/QuadTiles/*
- tuning:  "default_statistics_target = 500" + Analyze data  ( see
https://pgtune.leopard.in.ua/  DB type: Data warehouse )
- high work memory  ( for big batches )
- hardware:  fast disk + large memory + strong cpu
- using the latest Postgres/PostGIS/Geos version  ( sometimes the current
'master')
- (pre)processing the data with the ETL tools  ( if possible )
- parallelisation:
http://blog.cleverelephant.ca/2019/05/parallel-postgis-4.html

Sometimes there are some performance tips in the blogs ..
- https://planet.postgis.net/
- https://planet.postgresql.org/

And I prefer the conference papers / tutorials ..
-
https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

or you can learn from other live projects
- there are some good OpenStreetMap projects on the github ..

best,
 Imre


Shaozhong SHI <shishaozhong at gmail.com> ezt írta (időpont: 2020. júl. 27.,
H, 23:34):

> It has been found that issues occur when Big Data is being handled with
> PostGIS.  Typically, queries can be killed by the system or memory is out.
> Often, queries can be very slow.  Sometimes, it will take days or weeks to
> complete.
>
> What are the best approaches and means for improving the performance of
> queries and processes in PostgreSQL/PostGIS?
>
> Can anyone shed light on this?
>
> Regards,
>
> Shao
> _______________________________________________
> 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/20200728/4b94d3f0/attachment.html>