[postgis-devel] I'd like to remove this Toast discussion

Regina Obe lr at pcorp.us
Thu Jun 29 22:31:59 PDT 2017


https://postgis.net/docs/performance_tips.html#small_tables_large_objects

This is the last bit of business before I can release PostGIS 2.3.3.

I'd like to get rid of this whole discussion of TOAST as I don't think it's
relevant anymore.

Am I correct in assuming that?

My assumption of why I think it's not relevant anymore is that we store
cached boxes for most geometries except for Points and 2 point lines. 
So isn't it so that even if there is no index on a geometry, we do not need
to fully detoast (or possibly not to detoast at all) the geometry to read
the bounding box.
That it is part of the head of the geometry?  

I did a quick test of my states table - 52 records (granted they aren't
huge, but toast size is about 14 MB)

When I do something like below without forcing enable_seqscan = false; it
doesn't use an index (well sometimes it does even without urging)

SELECT stusps 
from tiger_data.state_all 
WHERE the_geom &&
ST_Buffer('0101000020AD100000242F4BF9462754C0BD0F7DA33F524340'::geometry,
5);

But whether it uses an index or not, the timing is still about the same -
under 10ms.

Though interestingly switching to 

SELECT stusps 
FROM tiger_data.state_all 
WHERE ST_Intersects(the_geom,
ST_Buffer('0101000020AD100000242F4BF9462754C0BD0F7DA33F524340'::geometry,
5));

Does always force an index scan.


Thanks,
Regina





More information about the postgis-devel mailing list