[postgis-devel] Proj Setup Costs

[Marco Boeringa]

Yes, storing the proj cache at the connection level, if technically 
reasonably feasible, seems the most sensible thing to do.

Although caching at the transaction level would likely solve my 
particalur performance issue, where I batch hundreds of SQL statements 
inside a single transaction, there are definitely use-cases thinkable 
that would not be helped by transaction level caching only.

Op 14-5-2021 om 01:07 schreef Paul Ramsey:
> So, as discussed in an earlier thread, the high set-up penalty of the proj_create_crs_to_crs() function in the new proj API can be exercised a lot if a PL/PgSQL function that calls ST_Transform() is used repeatedly, since each call to ST_Transform() inside the PL/PgSQL function will get a fresh FunctionCallInfo and thus a fresh cache, and have to re-lookup transformations that, in the ordinary course of events, would be waiting in cache.
>
> Solutions? The only thing to do is take the Proj cache information out of the FunctionCallInfo extra slot (aka fcinfo->fn_extra) and move it to a global so it ends up with a livespan as long as the connection. This means the cache itself might need to be smarter, since it has to deal with potentially a backend lifespan worth of cache entries rather than as many as it might get during a single function lifetime.
>
> I'm concerned that we may be mucking around and filling up system memory contexts with things that perhaps we shouldn't, though.
>
> For example, we're dumping all our constants into CacheMemoryContext, and we're doing it once per backend. I'm not sure that we're not slowly adding and then orphaning that constant information each time a backend runs through a lifecycle (connection->fork->work->die) since the cache is, perhaps, not per-backend but per cluster? Scary if true.
>
> Just storing thoughts for now, I have no best approach as yet.
>
> P
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel