> 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 <[email protected]> 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 > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
