obs_getmeta times out / goes over account limits for basic usage

[andy-esch]

Using the SQL API, cartoframes routinely constructs queries for OBS_GetMeta like the following, which is a very common use case to get all available measures in a small region (here the region is the size of Brooklyn) for a specific time span '2011 - 2015':

WITH envelope AS (
    SELECT ST_SetSRID(ST_Extent(the_geom), 4326) AS env, count(*)::int AS cnt FROM brooklyn_poverty
), numers AS (
    SELECT
        numer_id,
        null AS geom_id,
        '2011 - 2015' AS numer_timespan,
        null AS normalization
    FROM
        OBS_GetAvailableNumerators(
            (SELECT env FROM envelope),
            null,
            null,
            null,
            '2011 - 2015')

)
SELECT OBS_GetMeta(
envelope.env,
json_agg(numers),
10, 10, envelope.cnt
) AS meta
FROM numers, envelope
GROUP BY env, cnt

The dataset is here: https://team.carto.com/u/eschbacher/tables/brooklyn_poverty/public?redirected=true

cc @hannahblue @michellemho

[hannahblue]

cc @juanignaciosl This has a substantial impact on CARTOFrames usage

[antoniocarlon]

I think that the main problem here is that you are using OBS_GetMeta as a discovery function passing it all the available numerators found for a given envelope (more than 700 numerators) and this function is designed to create a well formed metadata JSON Object for OBS_GetData.

Can you use any of the available Discovery functions to do the job? For example in Builder, to get a query for OBS_GetMeta we are narrowing the options using (in this order) OBS_GetAvailableNumerators, OBS_GetAvailableDenominators, OBS_GetAvailableTimespans, OBS_GetAvailableGeometries, and then we perform the query against OBS_GetMeta/OBS_GetData.