<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=koi8-r"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:85.05pt 56.7pt 85.05pt 56.7pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1904827934;
mso-list-type:hybrid;
mso-list-template-ids:-1717269100 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Couple of things to try<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>1)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>Can you reduce the dataset down a bit to the point you can run it in a reasonable amount of time and see differences?<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>2)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>On the 2.4.3 plan I see it is running using parallel mode, can you turn off the parallel to rule that out as an issue. <o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>Set max_parallel_workers_per_gather = 0;<o:p></o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'>I don't know what your 2.4.2 was running, but for 2.3, it probably wouldn't run in parallel just because fewer functions were flagged as parallel safe<o:p></o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'>If you can reduce the set some something reasonable where we can test, I'd be happy to test on various OS/ PostGIS config.<o:p></o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'>I wouldn't rule out a regression issue on our end.<o:p></o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'>You can provide a sample on a postgis ticket, if it's not too top secret or just send directly to me.<o:p></o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'><a href="http://postgis.net/support/">http://postgis.net/support/</a><o:p></o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoListParagraph><span style='color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:.5in'><b>From:</b> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Jonas Nygaard Pedersen<br><b>Sent:</b> Monday, April 23, 2018 11:15 AM<br><b>To:</b> 'postgis-users@lists.osgeo.org' <postgis-users@lists.osgeo.org><br><b>Subject:</b> [postgis-users] Performance problems with ST_Union on postgres 9.6, postgis 2.4.3 running on top of Red Hat 7.4<o:p></o:p></p></div></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:.5in'>Hi list<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>I'm facing some performance issues when trying to execute the following query on a Red Hat 7.4 machine with Postgres 9.6 (PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit) and postgis 2.4.3:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>select st_multi((st_dump(geom)).geom)::geometry(multipolygon,25832) as geom<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> from (<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> select st_union(b.geom) as geom <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> from b.gdk_bygning b<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> ) foo;<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Previously the statement above finished in about 3.5 hours but now it seems to go on forever, and I'll cancel it after 3 days.<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>The table contains 5310482 rows and the CREATE TABLE statement looks like this:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>CREATE TABLE b.gdk_bygning<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>(<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> id_lokalid bigint,<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> objektstatus character varying(4000),<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> registreringfra timestamp(6) without time zone,<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> virkningfra timestamp(6) without time zone,<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> plannoejagtighed double precision,<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> vertikalnoejagtighed double precision,<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> bygninguuid character varying(4000),<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> bygningstype character varying(4000),<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> underminimumbygning character varying(5),<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> status character varying(4000),<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> geom geometry(Polygon,25832)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Indexes:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>CREATE INDEX gdk_bygning_id_lokalid_idx<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> ON b.gdk_bygning<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> USING btree<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> (id_lokalid);<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>CREATE INDEX sidx_gdk_bygning_geom<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> ON b.gdk_bygning<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> USING gist<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> (geom);<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>I'm not sure where the issue stems from, but from an approximation of when the issue started and near coincidental update from postgis 2.4.1 to 2.4.3, I suspect that the issue is rooted here, and probably combined with some clumsy settings in my postgresql.conf file.<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Below I have compiled some documentation that I think will be relevant:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> * the original Postgis install with 'yum', <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> * the update with 'yum', <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> * what I think are the relevant settings from my postgresql.conf, <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> * the query plan for the statement on the current 2.4.3 postgis version, <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> * and lastly the query plan on a windows machine running postgis 2.3.2 (I'm not able to roll back to 2.4.1 with GEOS 3.5).<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>The only thing that stands out to me is that the GEOS version is upgraded from 3.5.0 to 3.6.2 but I'm definitely unsure about what's going on and I hope that someone on the list can give me some advice.<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Regards Jonas <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>OUTPUT OF 'yum history info' FOR ORIGINAL INSTALL OF POSTGIS:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Loaded plugins: langpacks, product-id, rhnplugin, search-disabled-repos, subscription-manager<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>This system is receiving updates from RHN Classic or Red Hat Satellite.<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Transaction ID : 7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Begin time : Mon Oct 9 17:56:25 2017<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Begin rpmdb : 1379:99b8afbfabf2cf72ff17087d17a2a1607e29a909<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>End time : 17:56:35 2017 (10 seconds)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>End rpmdb : 1421:bd1fa883ae52216121f1a7c6a2eb06d2d6e36075<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>User : <b031513><span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Return-Code : Success<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Command Line : install postgis24_96.x86_64 postgis24_96-client.x86_64 postgis24_96-devel.x86_64 postgis24_96-utils.x86_64 SFCGAL.x86_64 pgrouting_96.x86_64<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Transaction performed with:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Installed rpm-4.11.3-25.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Updated subscription-manager-1.19.21-1.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Installed yum-3.4.3-154.el7.noarch @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA>Installed yum-metadata-parser-1.1.4-10.el7.x86_64 @anaconda/7.2<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> </span>Installed yum-rhn-plugin-2.0.1-9.el7.noarch @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Packages Altered:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install CGAL-4.7-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install CharLS-1.0-5.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Install SFCGAL-1.2.2-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install SFCGAL-libs-1.2.2-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install armadillo-4.320.0-1.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install arpack-3.1.3-2.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install atlas-3.10.1-12.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install blas-3.4.2-8.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install boost-atomic-1.53.0-27.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install boost-chrono-1.53.0-27.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install boost-serialization-1.53.0-27.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install cfitsio-3.370-1.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install freexl-1.0.0i-1.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install gdal-libs-1.11.4-10.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install geos-3.5.0-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install hdf5-1.8.12-8.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install lapack-3.4.2-8.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install libdap-3.13.1-2.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install libgeotiff-1.4.0-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install libgfortran-4.8.5-16.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install libgta-1.0.4-1.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install libquadmath-4.8.5-16.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install netcdf-4.3.3.1-5.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install ogdi-3.2.0-0.19.beta2.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install openjpeg2-2.1.0-7.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-Compress-Raw-Zlib-1:2.061-4.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-DBD-Pg-2.19.3-4.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-DBI-1.627-4.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-Data-Dumper-2.145-3.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-IO-Compress-2.061-2.el7.noarch @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-Net-Daemon-0.48-5.el7.noarch @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-PlRPC-0.2020-14.el7.noarch @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install perl-version-3:0.99.07-2.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Install pgrouting_96-2.5.0-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Install postgis24_96-2.4.0-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Install postgis24_96-client-2.4.0-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Install postgis24_96-devel-2.4.0-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Install postgis24_96-utils-2.4.0-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install proj-4.8.0-4.el7.x86_64 @rhel-x86_64-server-7-epel<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install unixODBC-2.3.1-11.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install xerces-c-3.1.1-8.el7_2.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>history info<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>OUTPUT OF 'yum history info' FOR THE UPDATE:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Loaded plugins: langpacks, product-id, rhnplugin, search-disabled-repos, subscription-manager<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>This system is receiving updates from RHN Classic or Red Hat Satellite.<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Transaction ID : 35<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Begin time : Tue Apr 3 11:30:58 2018<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Begin rpmdb : 1748:db6ede4f0b0b9815a1f8704452181b47f0a32796<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>End time : 11:31:04 2018 (6 seconds)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA>End rpmdb : 1749:83a1f934015ebd6db6adc07214b1937477780af4<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA>User : <b031513><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Return-Code : Success<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Command Line : update postgis<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Transaction performed with:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Installed rpm-4.11.3-25.el7.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Installed subscription-manager-1.19.23-1.el7_4.x86_64 @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Installed yum-3.4.3-154.el7.noarch @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA>Installed yum-metadata-parser-1.1.4-10.el7.x86_64 @anaconda/7.2<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> </span>Installed yum-rhn-plugin-2.0.1-9.el7.noarch @rhel-x86_64-server-7<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Packages Altered:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Dep-Install geos36-3.6.2-3.1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Updated postgis24_96-2.4.1-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Update 2.4.3-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Updated postgis24_96-client-2.4.1-1.rhel7.x86_64 @pgdg96<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA>Update 2.4.3-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> Updated postgis24_96-devel-2.4.1-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> Update 2.4.3-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> Updated postgis24_96-utils-2.4.1-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> Update 2.4.3-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA>history info<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> <o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> <o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>WHAT I THINK ARE THE RELEVANT OPTIONS IN MY 'postgresql.conf' THAT ARE DIFFERENT FROM THE ONE THAT CAME WITH 9.6 FROM 'pgdg96' REPOSITORY:<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>shared_buffers = 50GB # min 128kB<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> # (change requires restart)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#huge_pages = try # on, off, or try<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> # (change requires restart)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#temp_buffers = 8MB # min 800kB<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#max_prepared_transactions = 0 # zero disables the feature<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> # (change requires restart)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>work_mem = 5GB # min 64kB<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>maintenance_work_mem = 5GB<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#replacement_sort_tuples = 150000 # limits use of replacement selection sort<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA>#max_stack_depth = 2MB # min 100kB<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>dynamic_shared_memory_type = posix # the default is the first option<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>effective_io_concurrency = 200 # 1-1000; 0 disables prefetching<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>max_worker_processes = 64 # (change requires restart)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>max_parallel_workers_per_gather = 12 # taken from max_worker_processes<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>seq_page_cost = 1.0 # measured on an arbitrary scale<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>random_page_cost = 2.0 # same scale as above<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#cpu_tuple_cost = 0.01 # same scale as above<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#cpu_index_tuple_cost = 0.005 # same scale as above<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#cpu_operator_cost = 0.0025 # same scale as above<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>parallel_tuple_cost = 0.001 # same scale as above<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>parallel_setup_cost = 100.0 # same scale as above<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>#min_parallel_relation_size = 8MB<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>effective_cache_size = 90GB<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>QUERY PLAN AFTER UPDATE TO POSTGIS 2.4.3 ('POSTGIS="2.4.3 r16312" PGSQL="96" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" SFCGAL="1.2.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER')<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> QUERY PLAN<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>-----------------------------------------------------------------------------------------------------<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Subquery Scan on foo (cost=231732.65..231737.92 rows=1000 width=32)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> -> Aggregate (cost=231732.65..231732.66 rows=1 width=32)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> -> Gather (cost=100.00..218456.45 rows=5310482 width=169)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> Workers Planned: 5<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> -> Parallel Seq Scan on gdk_bygning b (cost=0.00..213045.96 rows=1062096 width=169)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>(5 rows)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>QUERY PLAN ON WINDOWS MACHINE ('POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.5, released 2016/07/01" LIBXML="2.9.4" TOPOLOGY RASTER'):<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> QUERY PLAN<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>-------------------------------------------------------------------------------------<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'>Subquery Scan on foo (cost=216558.28..216563.55 rows=1000 width=32)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA>-> Aggregate (cost=216558.28..216558.29 rows=1 width=32)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> -> Seq Scan on gdk_bygning b (cost=0.00..204498.02 rows=4824102 width=175)<span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'> <span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA style='font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA'>Jonas Nygaard Pedersen </span><span lang=DA style='font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA'>Geodataanalytiker</span><span lang=DA style='font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA'> </span><span lang=DA style='font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA'></span><span lang=DA style='font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA'>Eff – Effektivisering</span><span lang=DA style='font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA'></span><span lang=DA style='font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA'>Tel. 7254 5510</span><span lang=DA style='font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA'></span><span lang=DA style='font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA'>jonyp@sdfe.dk</span><span lang=DA><o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span lang=DA> <o:p></o:p></span></p></div></body></html>