<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
In the first sql below we just use "<span style="font-family: Monaco; font-size: 11px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 0); background: var(--white);">ST_Intersects(r1.geo,r2.geo)", </span><span style="color: rgb(0, 0, 0); font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; background: var(--white);">in
the second SQL we add <span style="font-family: Monaco; font-size: 11px; font-style: normal; font-variant-caps: normal; color: rgb(0, 0, 0); background: var(--white);">"r1.geo && r2.geo" </span></span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
The second sql executes 3 times faster probably because of parallel seems to kick in. (if we adjust parallell settings we can get the second SQL to run 6. times faster)</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Why is this happening when I just add <span style="caret-color: rgb(0, 0, 0); font-family: Monaco; font-size: 11px; background-color: rgb(255, 255, 255); display: inline !important">
"r1.geo && r2.geo" </span>?</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I have ran <span style="background: var(--white);">ANALYZE</span> on the table before running the tests and there are indexes on geo and gid.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures">Indexes:</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures"><span>
</span>"markslag_myrikilden_temp_gid_idx" UNIQUE, btree (gid)</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures"><span>
</span>"geoidx__markslag_myrikilden_temp___gist" gist (geo)</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures"><br>
</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures">We run on</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures"></span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures"></span></p>
<ul>
<li><span style="background: var(--white);">PostgreSQL 11.5 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-</span><span style="background: var(--white);">36), 64-bit</span><br>
</li><li><span style="background: var(--white);">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000"><span style="font-variant-ligatures: no-common-ligatures">POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="110" GEOS="3.8.0dev-CAPI-1.12.0 " SFCGAL="1.3.7" PROJ="Rel. 6.2.0, September 1st,
2019" GDAL="GDAL 3.1.0dev-7a9a0f4-dirty, released 2019/99/99" LIBXML="2.9.1" TOPOLOGY RASTER</span></p>
</span></li></ul>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">1. SQL :</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">EXPLAIN ANALYZE</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">select</span> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext, ST_Collect(geo)
<span style="color: #7f0055">as</span> geo<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; color: #7f0055">from<span style="color: #000000"> (</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">select</span> r1.gid, r1.atil, r1.myr, r1.myrtype,r1.myromdanning,r1.myrtypetext,r1.myromdanningtext,r1.geo,
<span style="color: #4000c8">true</span> doUnion</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">from</span> sde_markslag.markslag_myrikilden_temp r1,<span>
</span>sde_markslag.markslag_myrikilden_temp r2</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">where</span> ST_Intersects(r1.geo,r2.geo)
<span style="color: #7f0055">and</span> r1.gid != r2.gid</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">) <span style="color: #7f0055">
as</span> r</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">group</span>
<span style="color: #7f0055">by</span> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; color: #408080">-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>HashAggregate<span>
</span>(cost=10519178.25..10519180.65 <span style="color: #7f0055">rows</span>=192 width=46) (actual
<span style="color: #7f0055">time</span>=185774.300..187207.628 <span style="color: #7f0055">
rows</span>=163 loops=1)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>Group Key: r1.atil, r1.myr, r1.myrtype, r1.myromdanning, r1.myrtypetext, r1.myromdanningtext</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>-><span>
</span>Nested Loop<span> </span>(cost=0.29..9169289.37 <span style="color: #7f0055">
rows</span>=77136507 width=1612) (actual <span style="color: #7f0055">time</span>=0.680..183087.904
<span style="color: #7f0055">rows</span>=721668 loops=1)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>
-><span> </span>Seq Scan <span style="color: #7f0055">on</span> markslag_myrikilden_temp r1<span>
</span>(cost=0.00..59170.91 <span style="color: #7f0055">rows</span>=567241 width=1616) (actual
<span style="color: #7f0055">time</span>=0.021..640.233 <span style="color: #7f0055">
rows</span>=567241 loops=1)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>
-><span> </span>Index Scan <span style="color: #7f0055">using</span> geoidx__markslag_myrikilden_temp___gist
<span style="color: #7f0055">on</span> markslag_myrikilden_temp r2<span> </span>
(cost=0.29..15.87 <span style="color: #7f0055">rows</span>=19 width=1602) (actual
<span style="color: #7f0055">time</span>=0.257..0.317 <span style="color: #7f0055">
rows</span>=1 loops=567241)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>Index Cond: (r1.geo && geo)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>Filter: ((r1.gid <> gid) <span style="color: #7f0055">AND</span> _st_intersects(r1.geo, geo))</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>Rows Removed <span style="color: #7f0055">by</span> Filter: 2</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>Planning Time: 4.800 ms</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>Execution Time: 187214.730 ms</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">(10 <span style="color: #7f0055">
rows</span>)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">Time: 187231.267 ms (03:07.231)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; min-height: 15.0px">
<br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; min-height: 15.0px">
<br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; min-height: 15.0px">
<br>
</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">2. SQL :</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">EXPLAIN ANALYZE</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">select</span> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext, ST_Collect(geo)
<span style="color: #7f0055">as</span> geo<span> </span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; color: #7f0055">from<span style="color: #000000"> (</span></p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">select</span> r1.gid, r1.atil, r1.myr, r1.myrtype,r1.myromdanning,r1.myrtypetext,r1.myromdanningtext,r1.geo,
<span style="color: #4000c8">true</span> doUnion</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">from</span> sde_markslag.markslag_myrikilden_temp r1,<span>
</span>sde_markslag.markslag_myrikilden_temp r2</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">where</span> r1.geo && r2.geo
<span style="color: #7f0055">and</span> ST_Intersects(r1.geo,r2.geo) <span style="color: #7f0055">
and</span> r1.gid != r2.gid</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">) <span style="color: #7f0055">
as</span> r</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span style="color: #7f0055">group</span>
<span style="color: #7f0055">by</span> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext;</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; color: #408080">-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>HashAggregate<span>
</span>(cost=243692.31..243694.71 <span style="color: #7f0055">rows</span>=192 width=46) (actual
<span style="color: #7f0055">time</span>=62439.948..63863.421 <span style="color: #7f0055">
rows</span>=163 loops=1)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>Group Key: r1.atil, r1.myr, r1.myrtype, r1.myromdanning, r1.myrtypetext, r1.myromdanningtext</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>-><span>
</span>Gather<span> </span>(cost=1000.28..242721.48 <span style="color: #7f0055">
rows</span>=55476 width=1612) (actual <span style="color: #7f0055">time</span>=1.519..59526.211
<span style="color: #7f0055">rows</span>=721668 loops=1)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>
Workers Planned: 2</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>
Workers Launched: 2</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>
-><span> </span>Nested Loop<span> </span>(cost=0.29..236173.88 <span style="color: #7f0055">
rows</span>=23115 width=1612) (actual <span style="color: #7f0055">time</span>=0.583..59945.673
<span style="color: #7f0055">rows</span>=240556 loops=3)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>-><span> </span>Parallel Seq Scan <span style="color: #7f0055">on</span> markslag_myrikilden_temp r1<span>
</span>(cost=0.00..55862.00 <span style="color: #7f0055">rows</span>=236350 width=1616) (actual
<span style="color: #7f0055">time</span>=0.013..260.552 <span style="color: #7f0055">
rows</span>=189080 loops=3)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>-><span> </span>Index Scan <span style="color: #7f0055">using</span> geoidx__markslag_myrikilden_temp___gist
<span style="color: #7f0055">on</span> markslag_myrikilden_temp r2<span> </span>
(cost=0.29..0.75 <span style="color: #7f0055">rows</span>=1 width=1602) (actual <span style="color: #7f0055">
time</span>=0.252..0.311 <span style="color: #7f0055">rows</span>=1 loops=567241)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>Index Cond: ((r1.geo && geo) <span style="color: #7f0055">AND</span> (r1.geo && geo))</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>Filter: ((r1.gid <> gid) <span style="color: #7f0055">AND</span> _st_intersects(r1.geo, geo))</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span>
</span>Rows Removed <span style="color: #7f0055">by</span> Filter: 2</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>Planning Time: 7.791 ms</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco"><span> </span>Execution Time: 63871.265 ms</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">(13 <span style="color: #7f0055">
rows</span>)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco">Time: 63886.211 ms (01:03.886)</p>
<p style="margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; min-height: 15.0px">
<br>
</p>
<br>
Lars</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
</body>
</html>