<div dir="ltr"><div>Hi Shira,</div><div><br></div><div>Thanks for the plans! Didn't know there was a way to "reverse engineering" plans from the Depsez links :D<br></div><div><br></div><div>Looks you have enabled JIT compilation in the PostgreSQL 12 server (<a href="https://www.postgresql.org/docs/11/jit-reason.html">https://www.postgresql.org/docs/11/jit-reason.html</a>).</div><div>That could explain the speedup in query execution! <br></div><div><br></div><div>JIT compilation improve how tuple are "deformed" (i.e. the process of transforming on-disk tuples into in-memory representation),</div><div>so shared buffers population is optimised. During its first implementation in PostgreSQL there were tests showing how aggregations</div><div>could benefit from JIT compilation in order to be executed with a 40% less of time (<a href="https://www.postgresql.org/message-id/5a18282d-89d0-ba21-4d54-bc2259ad7f26%40postgrespro.ru">https://www.postgresql.org/message-id/5a18282d-89d0-ba21-4d54-bc2259ad7f26%40postgrespro.ru</a>).<br></div><div><br></div><div>I'm expecting that in PostgreSQL 12 things are even better!</div><div><br></div><div>As a test, you could try to disable JIT compilation in the PostgreSQL 12 DB server, and try again.<br></div><div><br></div><div>Hope this insight can help,</div><div>Giuseppe.<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Il giorno lun 13 gen 2020 alle ore 16:21 Shira Bezalel <<a href="mailto:shira@sfei.org">shira@sfei.org</a>> ha scritto:<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 style="font-family:tahoma,sans-serif">Hi Giuseppe,</div><div style="font-family:tahoma,sans-serif"><br></div><div 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 style="font-family:tahoma,sans-serif"><br></div><div style="font-family:tahoma,sans-serif">9.6</div><div style="font-family:tahoma,sans-serif"><br></div><div 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 style="font-family:tahoma,sans-serif"><br></div><div style="font-family:tahoma,sans-serif">12.1</div><div style="font-family:tahoma,sans-serif"><br></div><div 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 style="font-family:tahoma,sans-serif"><br></div><div 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 style="font-family:tahoma,sans-serif"><br></div><div style="font-family:tahoma,sans-serif">Thanks,</div><div 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" target="_blank">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 style="font-family:tahoma,sans-serif">Hi Giuseppe,</div><div style="font-family:tahoma,sans-serif"><br></div><div 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 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"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div><font size="2" face="tahoma, sans-serif" color="#666666">Shira Bezalel </font></div><div><font size="2" face="tahoma, sans-serif" color="#666666">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 size="2" face="tahoma, sans-serif" color="#666666">San Francisco Estuary Institute</font></div><div><font size="2" face="tahoma, sans-serif" color="#666666"><a href="http://www.sfei.org" target="_blank">www.sfei.org</a></font></div><div><font size="2" face="tahoma, sans-serif" color="#666666">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>
_______________________________________________<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></div>