Хранимая функция Postgresql иногда выполняется очень медленно

Question

У нас есть довольно большая функция plpgsql с оператором if- и elsif в PostgreSQL 9.4.4 Внутри каждого if-тела есть вызовы функций для функций stable-sql.

Мы вызываем функцию следующим образом:

SELECT * 
from rawdata.getNumbersForUserBasedMetricEventsGroupedByClient('2015-09-28','2015-10-28','{4}'::int[],2,null,null,null,null,null); 

Первые 4-5 раз функция выполняется довольно быстро, примерно за 2,5 секунды, но затем внезапно производительность резко падает и выполнение занимает около 7,5 секунд. Он остается на этом уровне для всех последовательных вызовов. Мы также пытались объявить функцию plpgsql стабильной, но это не помогло.

Когда мы вызываем одну из внутренних функций stable-sql напрямую, выполнение всегда занимает около 2,5 секунд.

Это схема таблицы rawdata.metricevent:

rawdata.metricevent (metriceventid bigint PRIMARY KEY,
                     metricevent integer,
                     client integer,
                     age integer,
                     country varchar(256),
                     userideventowner bigint,
                     contributoruserid bigint,
                     tournamentid bigint,
                     eventoccurtime timestamp,
                     iscounted boolean)

У нас есть индекс btree для столбца eventoccurtime. Без индекса btree разница еще больше, иногда выполнение завершается всего за несколько секунд, а иногда длится более 100 секунд.

Теперь наши вопросы: почему это так? Что происходит, когда функция plpgsql выполняется в 5-й или 6-й раз, почему это вдруг занимает так много времени? Кстати, загрузка ЦП также очень высока для этих запросов. Мы также проанализировали запрос с помощью EXPLAIN ANALYZE, и планировщик запросов ВСЕГДА занимает около 0,034 мс, но выполнение запроса отличается от 2,5 до 7,5 секунд. И он также никогда не находится где-то посередине, его 2,5 секунды или 7,5 секунд.

Это функция Main-pgpsql с переменным временем выполнения и функция stable-sql ниже с постоянным временем выполнения.

CREATE OR REPLACE FUNCTION rawdata.getNumbersForUserBasedMetricEventsGroupedByClient(pFrom timestamp, pTo timestamp, pMetricEvent integer[], pTimeDomainType integer,
                                                                            pCountry varchar(100),pAgeFrom integer,pAgeTo integer,pUserlanguage varchar(50),pTournamentlanguage varchar(50))
RETURNS TABLE(dfrom timestamp, x bigint, y bigint, xx bigint, yy bigint)
AS $$
BEGIN
    IF pTimeDomainType = 1 THEN
        --hours
        RETURN QUERY
            SELECT * FROM rawdata.getNumbersForUBMetricEventsGroupedByClientPerHours(pFrom,pTo,pMetricEvent,pCountry,pAgeFrom,pAgeTo,pUserLanguage,pTournamentLanguage);
    ELSIF pTimeDomainType = 2 THEN
        --days
        RETURN QUERY
            SELECT * FROM rawdata.getNumbersForUBMetricEventsGroupedByClientPerDays(pFrom,pTo,pMetricEvent,pCountry,pAgeFrom,pAgeTo,pUserLanguage,pTournamentLanguage);

    ELSIF pTimeDomainType = 3 THEN
        --week
        RETURN QUERY
            SELECT * FROM rawdata.getNumbersForUBMetricEventsGroupedByClientPerWeeks(pFrom,pTo,pMetricEvent,pCountry,pAgeFrom,pAgeTo,pUserLanguage,pTournamentLanguage);
    ELSIF pTimeDomainType = 4 THEN
        --month
        RETURN QUERY
            SELECT * FROM rawdata.getNumbersForUBMetricEventsGroupedByClientPerMonths(pFrom,pTo,pMetricEvent,pCountry,pAgeFrom,pAgeTo,pUserLanguage,pTournamentLanguage);   
    END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION rawdata.getNumbersForUBMetricEventsGroupedByClientPerHours(pFrom timestamp, pTo timestamp, pMetricEvent integer[],
                                                                            pCountry varchar(100),pAgeFrom integer,pAgeTo integer,pUserlanguage varchar(50),pTournamentlanguage varchar(50))
RETURNS TABLE(dfrom timestamp, x bigint, y bigint, xx bigint, yy bigint)
AS $$
        SELECT hours timedomain,count(distinct em.userideventowner) as x,count(distinct ef.userideventowner) as y,count(distinct emh.userideventowner) as xx,count(distinct efh.userideventowner) as yy
        FROM generate_series
                ( pFrom::timestamp 
                , pTo::timestamp + '23 hour'
                , '1 hour'::interval) hours
           LEFT JOIN rawdata.metricevent e1 ON e1.eventoccurtime >=pFrom 
                                       AND e1.eventoccurtime < pTo + '1 day'
                                       AND (e1.metricevent = ANY (pMetricEvent))
                                       AND (e1.country = pCountry OR pCountry is null)
                                       AND (e1.age >= pAgeFrom OR pAgeFrom is null) AND (e1.age <= pAgeTo OR pAgeTo is null)
                                       AND userideventowner >= 110
                                       AND hours = date_trunc('hour',e1.eventoccurtime)
           LEFT JOIN rawdata.userlanguage ul ON e1.userideventowner = ul.userideventowner
                                                    AND (ul.userlanguage = pUserLanguage OR pUserLanguage is null)
           LEFT JOIN rawdata.metricevent ei ON e1.metriceventid = em.metriceventid AND ei.client=1
           LEFT JOIN rawdata.metricevent ea ON e1.metriceventid = ef.metriceventid AND ea.client=2
           LEFT JOIN rawdata.metricevent ew ON e1.metriceventid = emh.metriceventid AND ew.client=3
           LEFT JOIN rawdata.metricevent eww ON e1.metriceventid = efh.metriceventid AND eww.client=4
        GROUP BY hours
        ORDER BY hours;
$$
LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION rawdata.getNumbersForUBMetricEventsGroupedByClientPerDays(pFrom timestamp, pTo timestamp, pMetricEvent integer[],
                                                                            pCountry varchar(100),pAgeFrom integer,pAgeTo integer,pUserlanguage varchar(50),pTournamentlanguage varchar(50))
RETURNS TABLE(dfrom timestamp, x bigint, y bigint, xx bigint, yy bigint)
AS $$
        SELECT days timedomain,count(distinct em.userideventowner) as x,count(distinct ef.userideventowner) as y,count(distinct emh.userideventowner) as xx,count(distinct efh.userideventowner) as yy
        FROM generate_series
                ( pFrom::timestamp 
                , pTo::timestamp
                , '1 day'::interval) days
           LEFT JOIN rawdata.metricevent e1 ON e1.eventoccurtime >=pFrom 
                               AND e1.eventoccurtime < pTo + '1 day'
                               AND (e1.metricevent = ANY (pMetricEvent))
                               AND (e1.country = pCountry OR pCountry is null)
                               AND (e1.age >= pAgeFrom OR pAgeFrom is null) AND (e1.age <= pAgeTo OR pAgeTo is null)
                               AND userideventowner >= 110
                               AND days = date_trunc('day',e1.eventoccurtime)                          
           LEFT JOIN rawdata.userlanguage ul ON e1.userideventowner = ul.userideventowner
                                            AND (ul.userlanguage = pUserLanguage OR pUserLanguage is null)
           LEFT JOIN rawdata.metricevent ei ON e1.metriceventid = em.metriceventid AND ei.client=1
           LEFT JOIN rawdata.metricevent ea ON e1.metriceventid = ef.metriceventid AND ea.client=2
           LEFT JOIN rawdata.metricevent ew ON e1.metriceventid = emh.metriceventid AND ew.client=3
           LEFT JOIN rawdata.metricevent eww ON e1.metriceventid = efh.metriceventid AND eww.client=4
        GROUP BY days
        ORDER BY days;
$$
LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION rawdata.getNumbersForUBMetricEventsGroupedByClientPerWeeks(pFrom timestamp, pTo timestamp, pMetricEvent integer[],
                                                                            pCountry varchar(100),pAgeFrom integer,pAgeTo integer,pUserlanguage varchar(50),pTournamentlanguage varchar(50))
RETURNS TABLE(dfrom timestamp, x bigint, y bigint, xx bigint, yy bigint)
AS $$
        SELECT min(days) timedomain,count(distinct em.userideventowner) as x,count(distinct ef.userideventowner) as y,count(distinct emh.userideventowner) as xx,count(distinct efh.userideventowner) as yy
                FROM generate_series
                        ( pFrom::timestamp 
                        , pTo::timestamp
                        , '1 day'::interval) days
                   LEFT JOIN rawdata.metricevent e1 ON e1.eventoccurtime >=pFrom 
                                       AND e1.eventoccurtime < pTo + '1 day'
                                       AND (e1.metricevent = ANY (pMetricEvent)) 
                                       AND (e1.country = pCountry OR pCountry is null)
                                       AND (e1.age >= pAgeFrom OR pAgeFrom is null) AND (e1.age <= pAgeTo OR pAgeTo is null)
                                       AND userideventowner >= 110
                                       AND days = date_trunc('day',e1.eventoccurtime)
                   LEFT JOIN rawdata.userlanguage ul ON e1.userideventowner = ul.userideventowner
                                                    AND (ul.userlanguage = pUserLanguage OR pUserLanguage is null)
                   LEFT JOIN rawdata.metricevent ei ON e1.metriceventid = em.metriceventid AND ei.client=1
                   LEFT JOIN rawdata.metricevent ea ON e1.metriceventid = ef.metriceventid AND ea.client=2
                   LEFT JOIN rawdata.metricevent ew ON e1.metriceventid = emh.metriceventid AND ew.client=3
                   LEFT JOIN rawdata.metricevent eww ON e1.metriceventid = efh.metriceventid AND eww.client=4
                GROUP BY EXTRACT(WEEK FROM days)
                ORDER BY 1;
$$
LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION rawdata.getNumbersForUBMetricEventsGroupedByClientPerMonths(pFrom timestamp, pTo timestamp, pMetricEvent integer[],
                                                                            pCountry varchar(100),pAgeFrom integer,pAgeTo integer,pUserlanguage varchar(50),pTournamentlanguage varchar(50))
RETURNS TABLE(dfrom timestamp, x bigint, y bigint, xx bigint, yy bigint)
AS $$
        SELECT min(days) timedomain,count(distinct em.userideventowner) as x,count(distinct ef.userideventowner) as y,count(distinct emh.userideventowner) as xx,count(distinct efh.userideventowner) as yy
                FROM generate_series
                        ( pFrom::timestamp 
                        , pTo::timestamp
                        , '1 day'::interval) days
                   LEFT JOIN rawdata.metricevent e1 ON e1.eventoccurtime >=pFrom 
                                       AND e1.eventoccurtime < pTo + '1 day'
                                       AND (e1.metricevent = ANY (pMetricEvent)) 
                                       AND (e1.country = pCountry OR pCountry is null)
                                       AND (e1.age >= pAgeFrom OR pAgeFrom is null) AND (e1.age <= pAgeTo OR pAgeTo is null)
                                       AND userideventowner >= 110
                                       AND days = date_trunc('day',e1.eventoccurtime)
                   LEFT JOIN rawdata.userlanguage ul ON e1.userideventowner = ul.userideventowner
                                                    AND (ul.userlanguage = pUserLanguage OR pUserLanguage is null)LEFT JOIN rawdata.metricevent ei ON e1.metriceventid = em.metriceventid AND ei.client=1
                   LEFT JOIN rawdata.metricevent ea ON e1.metriceventid = ef.metriceventid AND ea.client=2
                   LEFT JOIN rawdata.metricevent ew ON e1.metriceventid = emh.metriceventid AND ew.client=3
                   LEFT JOIN rawdata.metricevent eww ON e1.metriceventid = efh.metriceventid AND eww.client=4
                GROUP BY EXTRACT(MONTH FROM days)
                ORDER BY 1;         
$$
LANGUAGE sql STABLE;

С наилучшими пожеланиями, Томас