<div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif">Hi Giuseppe,</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">Here is the original explain output (You can also get this from clicking on the "Source" tab in the Depsez links):</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">9.6</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">HashAggregate (cost=42103.26..42105.26 rows=200 width=16) (actual time=94062.150..94062.161 rows=59 loops=1)<br> Group Key: (st_valuecount(cv.rast, 1, true, NULL::double precision[], '0'::double precision)).value<br> Buffers: shared hit=35395<br> -> Seq Scan on calveg_whrtype_20m cv (cost=0.00..11323.26 rows=2052000 width=12) (actual time=90.687..94056.706 rows=15812 loops=1)<br> Buffers: shared hit=35395<br>Planning time: 0.213 ms<br>Execution time: 94062.215 ms<br></div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">12.1</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">HashAggregate (cost=1088130.78..1088132.78 rows=200 width=16) (actual time=44634.021..44634.031 rows=59 loops=1)<br> Group Key: ((st_valuecount(cv.rast, 1, true, NULL::double precision[], '0'::double precision))).value<br> Buffers: shared hit=17664<br> -> Result (cost=0.00..1057350.78 rows=2052000 width=12) (actual time=348.651..44630.614 rows=15812 loops=1)<br> Buffers: shared hit=17664<br> -> ProjectSet (cost=0.00..10830.78 rows=2052000 width=32) (actual time=348.637..44628.307 rows=15812 loops=1)<br> Buffers: shared hit=17664<br> -> Seq Scan on calveg_whrtype_20m cv (cost=0.00..47.52 rows=2052 width=31) (actual time=0.022..1.311 rows=2052 loops=1)<br> Buffers: shared hit=27<br>Planning Time: 0.381 ms<br>JIT:<br> Functions: 12<br> Options: Inlining true, Optimization true, Expressions true, Deforming true<br> Timing: Generation 3.299 ms, Inlining 10.618 ms, Optimization 124.105 ms, Emission 80.956 ms, Total 218.978 ms<br>Execution Time: 44637.499 ms<br></div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">Interesting about the stats improvement in 10. If/when I gain more insight about this question, I'll keep this listed posted.</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">Thanks,</div><div class="gmail_default" style="font-family:tahoma,sans-serif">Shira</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, Jan 11, 2020 at 3:54 AM Giuseppe Broccolo <<a href="mailto:g.broccolo.7@gmail.com">g.broccolo.7@gmail.com</a>> wrote:<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="auto">Hi Shira,<br><br><div class="gmail_quote" dir="auto"><div dir="ltr" class="gmail_attr">On Fri, 10 Jan 2020, 21:44 Shira Bezalel, <<a href="mailto:shira@sfei.org" target="_blank">shira@sfei.org</a>> wrote:<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"><div class="gmail_default" style="font-family:tahoma,sans-serif">Hi Giuseppe,</div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">Thank you for your reply. I provided the EXPLAIN (ANALYZE ON, BUFFERS ON) output in the links in my initial email. Is that not what you meant?</div></div></blockquote></div><div dir="auto"><br></div><div dir="auto">I was meaning the vanilla output returned by the EXPLAIN, not just the (optimal, BTW) Depesz one. </div><div dir="auto"><br></div><div class="gmail_quote" dir="auto"><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"><div class="gmail_default" style="font-family:tahoma,sans-serif">Yes, the config between the two systems is very similar. I'm thinking the shared buffers count differs between the two systems because the row counts themselves are different. But why is that? I think I'll turn this question over to the general Postgres Performance list now.</div></div></blockquote></div><div dir="auto"><br></div><div dir="auto">If the tables are identical in the two system, a different count of rows could be to a better statistical sampling, so the planner is more accurate in planning the aggregation. And this could make sense cause since PostgreSQL 10 statistics have been improved. </div><div dir="auto"><br></div><div dir="auto">Also, consider that even slight differences between the systems can lead to significant differences. Would be good here to understand what is different in the configuration.</div><div dir="auto"><br></div><div dir="auto">Anyway, please keep us updated from any insight if you find something :)</div><div dir="auto"><br></div><div dir="auto">Giuseppe.</div><div class="gmail_quote" dir="auto"></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><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div><font color="#666666" face="tahoma, sans-serif" size="2">Shira Bezalel </font></div><div><font color="#666666" face="tahoma, sans-serif" size="2">Database Administrator & Desktop Support </font><span style="color:rgb(102,102,102);font-family:tahoma,sans-serif;font-size:small">Manager</span></div><div><font color="#666666" face="tahoma, sans-serif" size="2">San Francisco Estuary Institute</font></div><div><font color="#666666" face="tahoma, sans-serif" size="2"><a href="http://www.sfei.org" target="_blank">www.sfei.org</a></font></div><div><font color="#666666" face="tahoma, sans-serif" size="2">Ph: 510-746-7304</font></div></div><div style="font-weight:bold;font-style:normal;font-variant:normal;line-height:20px;margin:0px"><br style="color:rgb(0,0,0);font-family:Tahoma;font-size:13px;font-weight:normal;line-height:normal"></div>
<div style="padding-top:8px">
</div></div></div></div></div></div></div>