generate-sqltomvt generates SQL function with poor performance

[matmorel]

Hi,

I'm using generate-sqltomvt like this:

generate-sqltomvt openmaptiles.yaml --key --postgis-ver 3.2 --function --fname=getmvt >> my_script.sql

The resulting function looks like this:

CREATE OR REPLACE FUNCTION public.getmvt(zoom integer, x integer, y integer)
  RETURNS mvt bytea, key text
  LANGUAGE sql
  STABLE STRICT
AS $function$
SELECT mvt, md5(mvt) AS key FROM (SELECT STRING_AGG(mvtl, '') AS mvt FROM (

-- Layers selection goes here

) AS all_layers) AS mvt_data
;
$function$
;

This function can take a very long time, especially when zoom value is low.
By doing a bit of trial and error, I've come with this:

CREATE OR REPLACE FUNCTION public.getmvt(zoom integer, x integer, y integer)
 RETURNS TABLE (tile bytea, key text)
 LANGUAGE PLPGSQL
 STABLE STRICT
AS $function$
BEGIN
	RETURN QUERY
	SELECT mvt as tile, md5(mvt) AS key FROM (SELECT STRING_AGG(mvtl, '') AS mvt FROM (

        -- Layers selection goes here

	) AS all_layers) AS mvt_data;
END
$function$
;

Which takes roughly the same time to execute as the layers selection query alone.
In my case a query that was taking around 1min takes now around 30ms !

I hope this helps.

[alex-kowalczyk]

@matmorel is that still valid point? Is there a complete patch to generate-sqltomvt anywhere by any chance?

[matmorel]

@alex-kowalczyk Sorry for the late response... I didn't retried it, but if the query didn't changed then the performance is still better with my way of doing it. I don't know if the generate-sqltomvt script have been updated, I know very little about python that's why I didn't created a PR.