[postgis-devel] ST_Subdivide Defaults

[Paul Ramsey]

That was actually my empirical test: doing a self-join of the table on itself. So my initial table had just 367 records in it, but it took 1500ms to do a self-join. After subdividing to 256, the table had 5300 records, but a self-join took just 520ms. At 128 the table had 10000 records, but a self-join was 500ms. At 96 there were 14000 records, self-join 480ms. At 64 there were 22000 records, self-join at 500ms. So the test was actually including the increasing number of records in the mix with the shriking object size. 

P.

> On Jan 21, 2022, at 10:15 AM, Marco Boeringa <marco at boeringa.demon.nl> wrote:
> 
> 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
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel