[postgis-devel] ST_Subdivide Defaults

[Marco Boeringa]

Hi Paul,

Interesting. I just wondered: does the smaller limit preventing TOAST 
always weigh up against the larger amount of records created, and 
especially if those records are part of tables having multiple indexes, 
and potentially already dozens of millions of records before the subdivide?

Marco

Op 21-1-2022 om 18:35 schreef Paul Ramsey:
> A user was talking about how much faster ST_Subdivide made their query, and their example was materializing the subdivided geometries into a table, building an index, and then using that downstream.
>
> Here's the thing: the default numVertices of ST_Subdivide is 256. Which (256 * 2 * 8 = 4096) is half of a full page, but actually kind of close to the TOAST threshold. At a minimum with standard storage defaults, it'll be getting compressed inline, if not actually TOASTed still. Which will hurt performance.
>
> I was thinking that a 128 or even 96 point default might make a lot more sense. At 128 vertices (2048 bytes) we're still flirting with inline compression, so maybe 96 (1536b) or even 64 vertices (1024b) would be best.
>
> In general, it seems like a missed upportunity to have something shredding down geometries but still be over-running the TOAST thresholds, that we know have performance implications, when the whole point of the shredding is improved performance.
>
> Thoughts?
>
> P
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel