pg_temp.tipg_catalog takes ~1 minute when there are large spatial tables

[hrodmn]

I have been using TiPG deployed as a Lambda function via the eoapi-cdk constructs and it is awesome! However, as the size of my spatial tables increases, the FastAPI application startup time exceeds the default timeout for the Lambda function (30 seconds). I am able to use TiPG successfully on the same database locally via uvicorn, but I can see that the application takes a minute to reach an active state.

My largest table has 3.6 million polygon geometries in it and has foreign keys on two other tables. On an identical database with only a few thousand records in that table the startup time is about 1 second! Has my deployment outgrown the Lambda option or if there is something I need to configure differently in TiPG (or even my database schema) to improve application performance? If I was only running the startup occasionally (i.e. on an always-on server) I don't think I would have a problem, but I would like to keep using Lambda if possible since we are not using the service very heavily right now.

I did some local debugging and can see that the pg_temp.tipg_catalog function is taking almost all of the startup time.

[vincentsarago]

@hrodmn In theory we could remove the internal catalog to list the collections dynamically (previous discussion: #37)

As of today, we don't have $ nor time to work on this.

The main issue might come from

tipg/tipg/sql/dbcatalog.sql

Lines 51 to 88 in e921266

	IF atttype IN ('timestamp', 'timestamptz') THEN
	EXECUTE FORMAT(
	$q$
	SELECT to_json(min(%I::timestamptz)), to_json(max(%I::timestamptz))
	FROM %s;
	$q$,
	attname,
	attname,
	att.attrelid::regclass::text
	) INTO attmin, attmax;
	ELSIF atttype IN ('geometry', 'geography') THEN
	geometry_type := postgis_typmod_type(att.atttypmod);
	srid = coalesce(nullif(postgis_typmod_srid(att.atttypmod),0), 4326);
	SELECT schemaname, relname, n_live_tup, n_mod_since_analyze
	INTO _schemaname, _relname, _n_live_tup, _n_mod_since_analyze
	FROM pg_stat_user_tables
	WHERE relid = att.attrelid;
	IF _n_live_tup > 0 AND _n_mod_since_analyze = 0 THEN
	bounds_geom := st_setsrid(st_estimatedextent(_schemaname, _relname, attname), srid);
	END IF;
	IF bounds_geom IS NULL THEN
	IF atttype = 'geography' THEN
	EXECUTE format('SELECT ST_SetSRID(ST_Extent(%I::geometry), %L) FROM %s', attname, srid, att.attrelid::regclass::text) INTO bounds_geom;
	ELSE
	EXECUTE format('SELECT ST_SetSRID(ST_Extent(%I), %L) FROM %s', attname, srid, att.attrelid::regclass::text) INTO bounds_geom;
	END IF;
	END IF;
	IF bounds_geom IS NOT NULL THEN
	IF srid != 4326 THEN
	bounds_geom := st_transform(bounds_geom, 4326);
	END IF;
	bounds = ARRAY[ st_xmin(bounds_geom), st_ymin(bounds_geom), st_xmax(bounds_geom), st_ymax(bounds_geom) ];
	END IF;
	END IF;

Where we try to get the spatial and temporal extents for each table! This should be fairly quick if there are indexes on both but maybe not.

Maybe we could add a settings to turn ON/OFF this step

cc @bitner

[hrodmn]

@vincentsarago thank you as always for the suggestions! Right now I do not have indexes set on either the geometry OR temporal columns in my spatial tables, so I will test that out first.

[vincentsarago]

I've an issue opened in eoAPI developmentseed/eoAPI#68

[hrodmn]

@vincentsarago your theory about the bottleneck is correct.

I added indexes to the spatial and temporal columns in my tables and was able to reduce the runtime for pg_catalog from ~70 seconds to ~40 seconds, so that does make a difference! However, that probably isn't good enough for running tipg as a Lambda function.

I commented out the lines that calculate the extents of the spatial and temporal columns and that brought startup time down to one second, so, for the purposes of running tipg in a serverless application, maybe a toggle to skip that step would be really useful.

Would I lose any functionality by skipping the extent calculations?

[vincentsarago]

I think it will make sense

We had a datatime_extent boolean in the past https://github.com/developmentseed/tipg/blob/main/tipg/settings.py#L53 which is not used any more but I think we could add both

datetime_extent and spatial_extent options in https://github.com/developmentseed/tipg/blob/main/tipg/settings.py#L151-L164

cc @bitner

[hrodmn]

I will take a crack at the changes this afternoon - PR incoming!