[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Issues of slow running queries when dealing with Big Data
From: Imre Samu <pella.samu () gmail ! com>
Date: 2020-07-28 1:24:54
Message-ID: CAJnEWwkk5BzK3rPccK+Aeu_EAnAm2Rm3z+fzqmx3fhWNH8zRFg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
> 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@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@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<div dir="ltr"><div>> What are the best approaches and means for improving the \
performance of queries and processes in \
PostgreSQL/PostGIS?<br></div><div><br></div><div><div>imho: no silver \
bullet</div><div>just some general tips + experience + practice(~learning with \
pain)</div>- <a href="https://postgis.net/docs/performance_tips.html" \
target="_blank">https://postgis.net/docs/performance_tips.html</a><div><div>- <a \
href="https://www.postgresql.org/docs/12/performance-tips.html" \
target="_blank">https://www.postgresql.org/docs/12/performance-tips.html</a></div><div>- \
<a href="https://wiki.postgresql.org/wiki/Performance_Optimization" \
target="_blank">https://wiki.postgresql.org/wiki/Performance_Optimization</a></div><div>- \
<a href="https://wiki.postgresql.org/wiki/Slow_Query_Questions" \
target="_blank">https://wiki.postgresql.org/wiki/Slow_Query_Questions</a></div><div><br></div><div>my \
favorite first line tricks:</div><div>- slim/optimal data model ( column types \
)<br>- materialised views<br></div><div>- partitioning !</div><div>- \
indexing</div><div>- processing/grouping data by Geohash/QuadTiles/*</div><div>- \
tuning: "default_statistics_target = 500" + Analyze data ( see <a \
href="https://pgtune.leopard.in.ua/">https://pgtune.leopard.in.ua/</a> DB type: \
Data warehouse )<br></div><div>- high work memory ( for big batches )</div><div>- \
hardware: fast disk <a class="gmail_plusreply" id="plusReplyChip-4">+</a> large \
memory + strong cpu</div><div></div><div>- using the latest Postgres/PostGIS/Geos \
version ( sometimes the current 'master') </div><div>- (pre)processing the \
data with the ETL tools ( if possible ) </div><div>- parallelisation: <a \
href="http://blog.cleverelephant.ca/2019/05/parallel-postgis-4.html">http://blog.cleve \
relephant.ca/2019/05/parallel-postgis-4.html</a></div><div><br></div><div>Sometimes \
there are some performance tips in the blogs ..</div><div>- <a \
href="https://planet.postgis.net/">https://planet.postgis.net/</a></div><div>- <a \
href="https://planet.postgresql.org/">https://planet.postgresql.org/</a></div><div><br></div><div>And \
I prefer the conference papers / tutorials ..</div><div>- <a \
href="https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations">ht \
tps://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations</a></div><div><br></div><div>or \
you can learn from other live projects</div><div>- there are some good OpenStreetMap \
projects on the github .. </div><div><br></div><div>best,</div><div> \
Imre</div><div><br></div><div></div></div></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">Shaozhong SHI <<a \
href="mailto:shishaozhong@gmail.com">shishaozhong@gmail.com</a>> ezt írta \
(időpont: 2020. júl. 27., H, 23:34):<br></div><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">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.<div><br></div><div>What are the best approaches and \
means for improving the performance of queries and processes in \
PostgreSQL/PostGIS?</div><div><br></div><div>Can anyone shed light on \
this?</div><div><br></div><div>Regards,</div><div><br></div><div>Shao</div></div> \
_______________________________________________<br> postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic