<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: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=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
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:12.0pt;
font-family:"Times New Roman",serif;}
span.hoenzb
{mso-style-name:hoenzb;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1908103614;
mso-list-type:hybrid;
mso-list-template-ids:-2013348314 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=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>You probably pushed it aside because all the solutions sound painful and in the large scheme of things, it probably isn't that big of a deal compared to other issues we have.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I actually think we make too big of a deal of it in the docs. I don't think it affects that many people to warrant it being in front and center cover.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>1)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>First of all, am I correct in saying this issue only affects folks where they happen to have so few records relative toast size that the planner chooses not to use an index scan.<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>2)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For a table that big with so few records, I bet it is used often in which case, it is pushed into shared memory and already detoasted. So the pain might only be felt once.<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>3)<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I suspect index-only scan logic also mitigates some of the issue encouraging it in many cases to use the index.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-devel [mailto:postgis-devel-bounces@lists.osgeo.org] <b>On Behalf Of </b>Paul Ramsey<br><b>Sent:</b> Friday, June 30, 2017 11:52 AM<br><b>To:</b> PostGIS Development Discussion <postgis-devel@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-devel] I'd like to remove this Toast discussion<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><p class=MsoNormal style='margin-left:.5in'>I feel like if we did it PgSQL side we'd have to be further up the chain, working around the area of detoast_datum_slice. We might also need a change to the decompressor though, to support partial decompression. Annoying, I know I've looked at this problem several times and put it aside each time, but I cannot remember why :)<br><br>P<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><p class=MsoNormal style='margin-left:.5in'>On Fri, Jun 30, 2017 at 8:44 AM, Javier Santana <<a href="mailto:jsantana@carto.com" target="_blank">jsantana@carto.com</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><p class=MsoNormal style='margin-left:.5in'>When I implemented the st_getgeometrytype version [1] (that Paul finally merged into postgis master) it was faster but Paul is right. My proposal here would be to change the pglz_decompress [2] function, called from PG_DETOAST_DATUM_SLICE (which is the macro we use to get a slice of the data). <o:p></o:p></p><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>The algorithm seems easy enough to change and it might get into postgres 10.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>In any case, there are two things we could do to improve this:<br>1) use the same pglz_compress postgres has but calling it from postgis source and have a HEADER + COMPRESSED_GEOMETRY<br>2) if we do that, we could encode the geometry by component so it compress better<span style='font-size:9.0pt;font-family:Consolas;color:#6A737D'> </span><o:p></o:p></p></div><div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>Cheers!<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>[1] <a href="https://github.com/javisantana/postgis/commit/fbc45b0486be558f77b8c89de0f3f30274be2b90" target="_blank">https://github.com/javisantana/postgis/commit/fbc45b0486be558f77b8c89de0f3f30274be2b90</a><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>[2] <a href="https://github.com/postgres/postgres/blob/master/src/common/pg_lzcompress.c#L682" target="_blank">https://github.com/postgres/postgres/blob/master/src/common/pg_lzcompress.c#L682</a><o:p></o:p></p></div></div></div><div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><div><p class=MsoNormal style='margin-left:.5in'>On Fri, Jun 30, 2017 at 5:18 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><div><p class=MsoNormal style='margin-left:.5in'>On Thu, Jun 29, 2017 at 10:31 PM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal style='margin-left:.5in'><a href="https://postgis.net/docs/performance_tips.html#small_tables_large_objects" target="_blank">https://postgis.net/docs/performance_tips.html#small_tables_large_objects</a><br><br>This is the last bit of business before I can release PostGIS 2.3.3.<br><br>I'd like to get rid of this whole discussion of TOAST as I don't think it's<br>relevant anymore.<br><br>Am I correct in assuming that?<br><br>My assumption of why I think it's not relevant anymore is that we store<br>cached boxes for most geometries except for Points and 2 point lines.<br>So isn't it so that even if there is no index on a geometry, we do not need<br>to fully detoast (or possibly not to detoast at all) the geometry to read<br>the bounding box.<br>That it is part of the head of the geometry?<o:p></o:p></p></blockquote><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div></div></div></div><div><div><div><div><p class=MsoNormal style='margin-left:.5in'>No, that is not so. If the object is large enough that it requires toasting, the whole thing is stuffed off to the side table and replaced with a reference in the main tuple. In order to get at that bbox on the header, we have to .... go to the toast tables and re-assemble the thing. <o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>"Worse", since we use compression on large objects, the object is compressed in the side tables and is pulled back in its entirety, then decompressed, then just the header is read.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>Changing that would involve storing objects uncompressed and implementing any compression we wanted internal to the postgis library. So we'd have some logic to compress anything larger than N vertices, but would always leave the header uncompressed so we could peak at it for free.<o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>That would *still* leave the toasting problem, which I'd have to re-confirm whether a pg_datum_slice cleanly pulls out just one piece of the toasted object or actually pulls the whole thing back first.<o:p></o:p></p></div></div></div></div><div><div><div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'>P.<o:p></o:p></p></div></div></div></div><div><div><div><div><p class=MsoNormal style='margin-left:.5in'> <o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal style='margin-left:.5in'><br>I did a quick test of my states table - 52 records (granted they aren't<br>huge, but toast size is about 14 MB)<br><br>When I do something like below without forcing enable_seqscan = false; it<br>doesn't use an index (well sometimes it does even without urging)<br><br>SELECT stusps<br>from tiger_data.state_all<br>WHERE the_geom &&<br>ST_Buffer('0101000020AD100000242F4BF9462754C0BD0F7DA33F524340'::geometry,<br>5);<br><br>But whether it uses an index or not, the timing is still about the same -<br>under 10ms.<br><br>Though interestingly switching to<br><br>SELECT stusps<br>FROM tiger_data.state_all<br>WHERE ST_Intersects(the_geom,<br>ST_Buffer('0101000020AD100000242F4BF9462754C0BD0F7DA33F524340'::geometry,<br>5));<br><br>Does always force an index scan.<br><br><br>Thanks,<br>Regina<br><br><br>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div></div></div><p class=MsoNormal style='margin-left:.5in'>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div></div></div><div><p class=MsoNormal style='margin-left:.5in'><span style='color:#888888'>-- <o:p></o:p></span></p></div><div><div><p class=MsoNormal style='margin-left:.5in'><span style='font-family:"Helvetica",sans-serif;color:#888888'>-- CARTO CTO </span><span style='color:#888888'><o:p></o:p></span></p></div></div><p class=MsoNormal style='margin-left:.5in'><br>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div></div></body></html>