TempResultFiles routine/db-related statistics

[FlatErikk]

• copy sql queries and result here
• advance this section with short and concise notes for potential enhancements and logging

Queries

Filesize sum + Filter

SELECT SUM(st_size_nc::float) FROM public.climate_tempresultfile where nc_meta->'bands' is not null;

Show all categories (foldertypes)

SELECT DISTINCT category FROM public.climate_tempresultfile

Count all entries

SELECT count(*) AS exact_count FROM public.climate_tempresultfile;

Count for generated tifs

SELECT count(*) filter (where st_mtime_tif is not null) as tif_nulls FROM public.climate_tempresultfile;

Statistics Overview

26th of March 2025 -- State

• 25526 database objects
• 1638 where nc_meta is null 23888 where -not
• 10 files have a tif generated
• 4595 GigaByte || 4594486545259 Bytes (overall filesum)
• 37 GigaByte || 37237037336 Bytes (overall filesum of files where we have read nc_meta)

Notes

• with these metrics it should be clear now that an Init process for all TempResultFiles potentially reads 23888 files (overall size of ~37 GB) with gdalinfo and potentially alters 23888 database objects this is time consuming and will take at least some minutes but can be much quicker as of right now
• Goal -> fix bottleneck of this process and define a clear log