<html>
<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:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
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:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:3.0cm 2.0cm 3.0cm 2.0cm;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1464812765;
mso-list-type:hybrid;
mso-list-template-ids:-331587952 1262116834 67502083 67502085 67502081 67502083 67502085 67502081 67502083 67502085;}
@list l0:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:83.25pt;
text-indent:-18.0pt;
font-family:Symbol;
mso-fareast-font-family:Calibri;
mso-bidi-font-family:"Times New Roman";}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:119.25pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:155.25pt;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:191.25pt;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:227.25pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:263.25pt;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:299.25pt;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:335.25pt;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
margin-left:371.25pt;
text-indent:-18.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></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="DA" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US">Hi list<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">select st_multi((st_dump(geom)).geom)::geometry(multipolygon,25832) as geom<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> from (<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> select st_union(b.geom) as geom <o:p>
</o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> from b.gdk_bygning b<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> ) foo;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">The table contains 5310482 rows and the CREATE TABLE statement looks like this:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE TABLE b.gdk_bygning<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">(<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> id_lokalid bigint,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> objektstatus character varying(4000),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> registreringfra timestamp(6) without time zone,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> virkningfra timestamp(6) without time zone,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> plannoejagtighed double precision,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> vertikalnoejagtighed double precision,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> bygninguuid character varying(4000),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> bygningstype character varying(4000),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> underminimumbygning character varying(5),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> status character varying(4000),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> geom geometry(Polygon,25832)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Indexes:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE INDEX gdk_bygning_id_lokalid_idx<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> ON b.gdk_bygning<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> USING btree<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> (id_lokalid);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE INDEX sidx_gdk_bygning_geom<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> ON b.gdk_bygning<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> USING gist<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> (geom);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Below I have compiled some documentation that I think will be relevant:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> * the original Postgis install with 'yum',
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> * the update with 'yum',
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> * what I think are the relevant settings from my postgresql.conf,
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> * the query plan for the statement on the current 2.4.3 postgis version,
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> * 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).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Regards Jonas <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">OUTPUT OF 'yum history info' FOR ORIGINAL INSTALL OF POSTGIS:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Loaded plugins: langpacks, product-id, rhnplugin, search-disabled-repos, subscription-manager<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">This system is receiving updates from RHN Classic or Red Hat Satellite.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Transaction ID : 7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Begin time : Mon Oct 9 17:56:25 2017<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Begin rpmdb : 1379:99b8afbfabf2cf72ff17087d17a2a1607e29a909<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">End time : 17:56:35 2017 (10 seconds)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">End rpmdb : 1421:bd1fa883ae52216121f1a7c6a2eb06d2d6e36075<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">User : <b031513><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Return-Code : Success<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Transaction performed with:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Installed rpm-4.11.3-25.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Updated subscription-manager-1.19.21-1.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Installed yum-3.4.3-154.el7.noarch @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> </span>Installed yum-metadata-parser-1.1.4-10.el7.x86_64 @anaconda/7.2<o:p></o:p></p>
<p class="MsoNormal"> <span lang="EN-US">Installed yum-rhn-plugin-2.0.1-9.el7.noarch @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Packages Altered:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install CGAL-4.7-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install CharLS-1.0-5.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Install SFCGAL-1.2.2-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install SFCGAL-libs-1.2.2-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install armadillo-4.320.0-1.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install arpack-3.1.3-2.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install atlas-3.10.1-12.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install blas-3.4.2-8.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install boost-atomic-1.53.0-27.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install boost-chrono-1.53.0-27.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install boost-serialization-1.53.0-27.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install cfitsio-3.370-1.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install freexl-1.0.0i-1.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install gdal-libs-1.11.4-10.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install geos-3.5.0-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install hdf5-1.8.12-8.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install lapack-3.4.2-8.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install libdap-3.13.1-2.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install libgeotiff-1.4.0-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install libgfortran-4.8.5-16.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install libgta-1.0.4-1.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install libquadmath-4.8.5-16.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install netcdf-4.3.3.1-5.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install ogdi-3.2.0-0.19.beta2.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install openjpeg2-2.1.0-7.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-Compress-Raw-Zlib-1:2.061-4.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-DBD-Pg-2.19.3-4.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-DBI-1.627-4.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-Data-Dumper-2.145-3.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-IO-Compress-2.061-2.el7.noarch @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-Net-Daemon-0.48-5.el7.noarch @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-PlRPC-0.2020-14.el7.noarch @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install perl-version-3:0.99.07-2.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Install pgrouting_96-2.5.0-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Install postgis24_96-2.4.0-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Install postgis24_96-client-2.4.0-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Install postgis24_96-devel-2.4.0-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Install postgis24_96-utils-2.4.0-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install proj-4.8.0-4.el7.x86_64 @rhel-x86_64-server-7-epel<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install unixODBC-2.3.1-11.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install xerces-c-3.1.1-8.el7_2.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">history info<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">OUTPUT OF 'yum history info' FOR THE UPDATE:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Loaded plugins: langpacks, product-id, rhnplugin, search-disabled-repos, subscription-manager<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">This system is receiving updates from RHN Classic or Red Hat Satellite.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Transaction ID : 35<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Begin time : Tue Apr 3 11:30:58 2018<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Begin rpmdb : 1748:db6ede4f0b0b9815a1f8704452181b47f0a32796<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">End time : 11:31:04 2018 (6 seconds)<o:p></o:p></span></p>
<p class="MsoNormal">End rpmdb : 1749:83a1f934015ebd6db6adc07214b1937477780af4<o:p></o:p></p>
<p class="MsoNormal">User : <b031513><o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US">Return-Code : Success<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Command Line : update postgis<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Transaction performed with:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Installed rpm-4.11.3-25.el7.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Installed subscription-manager-1.19.23-1.el7_4.x86_64 @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Installed yum-3.4.3-154.el7.noarch @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> </span>Installed yum-metadata-parser-1.1.4-10.el7.x86_64 @anaconda/7.2<o:p></o:p></p>
<p class="MsoNormal"> <span lang="EN-US">Installed yum-rhn-plugin-2.0.1-9.el7.noarch @rhel-x86_64-server-7<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Packages Altered:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Dep-Install geos36-3.6.2-3.1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Updated postgis24_96-2.4.1-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Update 2.4.3-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Updated postgis24_96-client-2.4.1-1.rhel7.x86_64 @pgdg96<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> </span>Update 2.4.3-1.rhel7.x86_64 @pgdg96<o:p></o:p></p>
<p class="MsoNormal"> Updated postgis24_96-devel-2.4.1-1.rhel7.x86_64 @pgdg96<o:p></o:p></p>
<p class="MsoNormal"> Update 2.4.3-1.rhel7.x86_64 @pgdg96<o:p></o:p></p>
<p class="MsoNormal"> Updated postgis24_96-utils-2.4.1-1.rhel7.x86_64 @pgdg96<o:p></o:p></p>
<p class="MsoNormal"> Update 2.4.3-1.rhel7.x86_64 @pgdg96<o:p></o:p></p>
<p class="MsoNormal">history info<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">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:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">shared_buffers = 50GB # min 128kB<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> # (change requires restart)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#huge_pages = try # on, off, or try<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> # (change requires restart)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#temp_buffers = 8MB # min 800kB<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#max_prepared_transactions = 0 # zero disables the feature<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> # (change requires restart)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">work_mem = 5GB # min 64kB<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">maintenance_work_mem = 5GB<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#replacement_sort_tuples = 150000 # limits use of replacement selection sort<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem<o:p></o:p></span></p>
<p class="MsoNormal">#max_stack_depth = 2MB # min 100kB<o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US">dynamic_shared_memory_type = posix # the default is the first option<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">effective_io_concurrency = 200 # 1-1000; 0 disables prefetching<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">max_worker_processes = 64 # (change requires restart)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">max_parallel_workers_per_gather = 12 # taken from max_worker_processes<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">seq_page_cost = 1.0 # measured on an arbitrary scale<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">random_page_cost = 2.0 # same scale as above<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#cpu_tuple_cost = 0.01 # same scale as above<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#cpu_index_tuple_cost = 0.005 # same scale as above<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#cpu_operator_cost = 0.0025 # same scale as above<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">parallel_tuple_cost = 0.001 # same scale as above<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">parallel_setup_cost = 100.0 # same scale as above<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">#min_parallel_relation_size = 8MB<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">effective_cache_size = 90GB<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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')<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> QUERY PLAN<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-----------------------------------------------------------------------------------------------------<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Subquery Scan on foo (cost=231732.65..231737.92 rows=1000 width=32)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> -> Aggregate (cost=231732.65..231732.66 rows=1 width=32)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> -> Gather (cost=100.00..218456.45 rows=5310482 width=169)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> Workers Planned: 5<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> -> Parallel Seq Scan on gdk_bygning b (cost=0.00..213045.96 rows=1062096 width=169)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">(5 rows)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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'):<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> QUERY PLAN<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-------------------------------------------------------------------------------------<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Subquery Scan on foo (cost=216558.28..216563.55 rows=1000 width=32)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> </span>-> Aggregate (cost=216558.28..216558.29 rows=1 width=32)<o:p></o:p></p>
<p class="MsoNormal"><span lang="EN-US"> -> Seq Scan on gdk_bygning b (cost=0.00..204498.02 rows=4824102 width=175)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA">Jonas Nygaard Pedersen
</span><span style="font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA">Geodataanalytiker</span><span style="font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA">
</span><span style="font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA"></span><span style="font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA">Eff – Effektivisering</span><span style="font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA"></span><span style="font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA">Tel.
7254 5510</span><span style="font-size:8.5pt;font-family:"Arial",sans-serif;color:#365F91;mso-fareast-language:DA"></span><span style="font-size:8.5pt;font-family:"Tahoma",sans-serif;color:#365F91;mso-fareast-language:DA">jonyp@sdfe.dk</span><span style="font-size:8.0pt;font-family:"Tahoma",sans-serif;mso-fareast-language:DA"><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>