Функция SQL очень медленная по сравнению с запросом без функциональной оболочки

У меня есть этот запрос PostgreSQL 9.4, который выполняется очень быстро (~ 12 мс):

SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email, 
  customers.name,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = 2
ORDER BY
  auth_web_events.id DESC;

Но если я встрою его в функцию, запрос будет выполняться очень медленно для всех данных, кажется, что он выполняется для каждой записи, что мне не хватает? У меня есть ~ 1 миллион данных, и я хочу упростить уровень моей базы данных, хранящий большие запросы в функции и представления.

CREATE OR REPLACE FUNCTION get_web_events_by_userid(int) RETURNS TABLE(
    id int,
    time_stamp timestamp with time zone,
    description text,
    origin text,
    userlogin text,
    customer text,
    client_ip inet
     ) AS
$func$
SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email AS user, 
  customers.name AS customer,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = $1
ORDER BY
  auth_web_events.id DESC;
  $func$ LANGUAGE SQL;

План запроса:

"Sort  (cost=20.94..20.94 rows=1 width=791) (actual time=61.905..61.906 rows=2 loops=1)"
"  Sort Key: auth_web_events.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=0.85..20.93 rows=1 width=791) (actual time=61.884..61.893 rows=2 loops=1)"
"        ->  Nested Loop  (cost=0.71..12.75 rows=1 width=577) (actual time=61.874..61.879 rows=2 loops=1)"
"              ->  Index Scan using auth_web_events_fk1 on auth_web_events  (cost=0.57..4.58 rows=1 width=61) (actual time=61.860..61.860 rows=2 loops=1)"
"                    Index Cond: (user_id_fk = 2)"
"              ->  Index Scan using auth_user_pkey on auth_user  (cost=0.14..8.16 rows=1 width=524) (actual time=0.005..0.005 rows=1 loops=2)"
"                    Index Cond: (id = 2)"
"        ->  Index Scan using customers_id_idx on customers  (cost=0.14..8.16 rows=1 width=222) (actual time=0.004..0.005 rows=1 loops=2)"
"              Index Cond: (id = auth_user.customer_id_fk)"
"Planning time: 0.369 ms"
"Execution time: 61.965 ms"

Я вызываю функцию таким образом:

SELECT * from get_web_events_by_userid(2)  

План запроса для функции:

"Function Scan on get_web_events_by_userid  (cost=0.25..10.25 rows=1000 width=172) (actual time=279107.142..279107.144 rows=2 loops=1)"
"Planning time: 0.038 ms"
"Execution time: 279107.175 ms"

РЕДАКТИРОВАТЬ: я просто меняю параметры, и проблема сохраняется.
РЕДАКТИРОВАТЬ2: План запроса для ответа Эрвина:

"Sort  (cost=20.94..20.94 rows=1 width=791) (actual time=0.048..0.049 rows=2 loops=1)"
"  Sort Key: w.id"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Nested Loop  (cost=0.85..20.93 rows=1 width=791) (actual time=0.030..0.037 rows=2 loops=1)"
"        ->  Nested Loop  (cost=0.71..12.75 rows=1 width=577) (actual time=0.023..0.025 rows=2 loops=1)"
"              ->  Index Scan using auth_user_pkey on auth_user u  (cost=0.14..8.16 rows=1 width=524) (actual time=0.011..0.012 rows=1 loops=1)"
"                    Index Cond: (id = 2)"
"              ->  Index Scan using auth_web_events_fk1 on auth_web_events w  (cost=0.57..4.58 rows=1 width=61) (actual time=0.008..0.008 rows=2 loops=1)"
"                    Index Cond: (user_id_fk = 2)"
"        ->  Index Scan using customers_id_idx on customers c  (cost=0.14..8.16 rows=1 width=222) (actual time=0.003..0.004 rows=1 loops=2)"
"              Index Cond: (id = u.customer_id_fk)"
"Planning time: 0.541 ms"
"Execution time: 0.101 ms"

person Mmeyer    schedule 30.01.2015    source источник
comment
Каков план запроса на первом? Использует ли он индекс?   -  person jpmc26    schedule 30.01.2015
comment
@jpmc26: Я не согласен с твоим советом. Помещение больших запросов в функции может быть чрезвычайно полезным, если сделано правильно. Зачастую гораздо удобнее поддерживать функцию внутри базы данных, где легче отслеживать зависимости. Обычно так быстрее. Приложению не нужно готовить сложные запросы для каждого сеанса — среди прочего, отправлять длинную строку запроса вместо простого вызова функции. Наилучший план действий зависит от полной картины.   -  person Erwin Brandstetter    schedule 30.01.2015
comment
Я только что добавил план запроса...   -  person Mmeyer    schedule 30.01.2015
comment
Ваша версия Postgres? Использует ли простой SQL тот же план запроса?   -  person Erwin Brandstetter    schedule 30.01.2015
comment
@ErwinBrandstetter Полагаю, я исхожу из точки зрения, что для большинства приложений небольшая экономия в скорости за счет повышенной сложности действительно того не стоит. Если вы не имеете дело с чем-то, что имеет огромные требования к производительности, время разработчика является одной из самых больших затрат. Кроме того, это затрудняет восстановление после сбоя развертывания, поскольку данные базы данных должны быть сохранены, в отличие от файлов приложения. (Исключения составляют бюрократические антиутопии.) Таким образом, размещение запроса в коде является разумным вариантом по умолчанию, и у вас должна быть конкретная причина поступить иначе.   -  person jpmc26    schedule 30.01.2015
comment
@ jpmc26: Вы продолжаете заявлять о повышении сложности, тогда как я вижу потенциал для снижения сложности. Приложению не нужно подготавливать (или, что еще хуже, объединять) запрос, достаточно вызвать хранимую процедуру. Ваш любимый тег — python, а ваши аргументы отражают мастерство. Мой основной опыт связан с Postgres, и у меня другая точка зрения. Вы обобщаете утверждения, основанные на вашей точке зрения, а не на фактических требованиях (неизвестного) варианта использования. Это обычная схема.   -  person Erwin Brandstetter    schedule 30.01.2015
comment
@ErwinBrandstetter Извините за неясность. Я не оспариваю то, что вы сказали о сложности того, что происходит во время выполнения. Я говорю о сложности разработки и поддержки приложения. Все, что я имею в виду, это то, что функция базы данных - это еще одна часть, которая может укусить вас и потребовать больше времени на разработку (как в случае с OP), и что изменения в БД труднее обрабатывать во время развертывания. Я полностью согласен, что есть случаи, когда функции имеют смысл, но описание OP предполагает, что они делают это скорее как передовую практику, не взвешивая недостатки.   -  person jpmc26    schedule 30.01.2015
comment
Странно. Я не вижу причин, по которым функция должна использовать другой план запроса. Очевидно, в этом нет ничего плохого. Как именно вы тестировали? У меня сейчас нет времени, извините.   -  person Erwin Brandstetter    schedule 30.01.2015
comment
@ jpmc26: Это огромная тема, над которой нужно подумать. Мастерство — важный фактор. Ваш последний комментарий кажется в основном приятным, даже с другой точки зрения.   -  person Erwin Brandstetter    schedule 30.01.2015
comment
Можете ли вы привести пример того, как вы вызываете функцию?   -  person Joe Love    schedule 30.01.2015
comment
Я подумал, что лучше поместить большой код sql в базу данных, если вы можете сделать это с помощью postgresql, почему бы и нет? таким образом, мое приложение может повысить производительность, а код моего приложения станет более аккуратным, я перехожу с mongodb и mysql и пытаюсь максимально использовать отличные функции postgresql.   -  person Mmeyer    schedule 30.01.2015
comment
ИМО, есть разница в том, как обрабатываются параметры. Функция не знает о фактических параметрах, поэтому она не может использовать статистику для создания плана.   -  person joop    schedule 30.01.2015
comment
@ErwinBrandstetter извините, я просто вставляю план запроса функции в свой вопрос.   -  person Mmeyer    schedule 30.01.2015
comment
Возможно, некоторые возможные совпадения с stackoverflow.com/questions/9305133/ за исключением того, что вы уже определили функцию как LANGUAGE sql. Но, по крайней мере, обсуждение этого вопроса может помочь объяснить, почему запрос выполняется по-разному внутри и вне контекста функции.   -  person rchang    schedule 30.01.2015
comment
@rchang Наоборот, это еще более запутанно. В ответе говорится, что с версии 9.2 запросы в функциях должны перепланироваться на основе входных значений. Таким образом, вы ожидаете, что они будут использовать один и тот же план.   -  person jpmc26    schedule 30.01.2015
comment
Странный. необработанный запрос имеет оценку строки rows=1, в то время как функция имеет оценку строки rows=1000, поэтому планы определенно не совпадают (даже несмотря на то, что мы на самом деле не можем видеть, каков план, когда он выполняется внутри функции). Это прозвучит странно, но что произойдет, если вы просто сделаете SELECT get_web_events_by_userid(2) и SELECT * FROM (SELECT get_web_events_by_userid(2)) x?   -  person jpmc26    schedule 30.01.2015
comment
Еще одна вещь, которую нужно проверить, действительно ли auth_web_events.user_id_fk является столбцом INT? (Звучит странно, я знаю, но стоит убедиться.)   -  person jpmc26    schedule 30.01.2015
comment
И зачем ограничивать auth_web_events.user_id_fk = $1 вместо `auth_user.id = $1`? (это происходит почти естественно, если вы переписываете в JOIN-синтаксис)   -  person joop    schedule 30.01.2015
comment
Это не имеет смысла. Функция SQL будет использовать индексы точно так же, как необработанный запрос, если ваша установка не будет серьезно неправильно сконфигурирована или повреждена. Это может объясняться несоответствием типов (как прокомментировал @jpmc26). Убедились ли вы, что оба вызова работают с одной и той же одной и той же базой данных? Вы уверены, что вызываете правильную функцию? Что вы получите, если запустите это? (только для отладки) SET enable_seqscan=f; SELECT * from get_web_events_by_userid(2);   -  person Erwin Brandstetter    schedule 30.01.2015
comment
Относительно возможного несоответствия типов: определения таблиц (точные, полные операторы CREATE TABLE или то, что вы получаете от \d tbl в psql) всегда полезны при вопросах производительности.   -  person Erwin Brandstetter    schedule 30.01.2015
comment
@ jpmc26 да auth_web_events.user_id_fk имеет столбец INT   -  person Mmeyer    schedule 30.01.2015


Ответы (2)


user

Переписывая вашу функцию, я понял, что вы добавили сюда псевдонимы столбцов:

SELECT 
  ...
  auth_user.email AS user, 
  customers.name AS customer,

.. что ничего не сделает с самого начала, поскольку эти псевдонимы невидимы вне функции и не упоминаются внутри функции. Поэтому их бы игнорировали. Для целей документации лучше использовать комментарий.

Но это также делает ваш запрос недействительным, поскольку user является полностью зарезервированное слово и не может использоваться в качестве псевдонима столбца, если только оно не заключено в двойные кавычки.

Как ни странно, в моих тестах функция работает с недопустимым псевдонимом. Вероятно, потому что он игнорируется (?). Но я не уверен, что это не может иметь побочных эффектов.

Ваша функция переписана (в противном случае эквивалентна):

CREATE OR REPLACE FUNCTION get_web_events_by_userid(int)
  RETURNS TABLE(
     id int
   , time_stamp timestamptz
   , description text
   , origin text
   , userlogin text
   , customer text
   , client_ip inet
  ) AS
$func$
SELECT w.id
     , w.time_stamp
     , w.description 
     , w.origin  
     , u.email     -- AS user   -- make this a comment!
     , c.name      -- AS customer
     , w.client_ip
FROM   public.auth_user       u
JOIN   public.auth_web_events w ON w.user_id_fk = u.id
JOIN   public.customers       c ON c.id = u.customer_id_fk 
WHERE  u.id = $1   -- reverted the logic here
ORDER  BY w.id DESC
$func$ LANGUAGE sql STABLE;

Очевидно, ключевое слово STABLE изменило результат. Волатильность функции не должна проблема в тестовой ситуации, которую вы описываете. Настройка обычно не используется для одного изолированного вызова функции. Прочитайте подробности в руководстве. Кроме того, стандартный EXPLAIN не показать планы запросов о том, что происходит внутри функций. Для этого вы можете использовать дополнительный модуль auto-explain:

У вас очень странное распределение данных:

Таблица auth_web_events содержит 100000000 записей, auth_user->2 записи, customers->1 запись

Поскольку вы не указали иное, предполагается, что функция возвращает приблизительно 1000 строк. Но на самом деле ваша функция возвращает только 2 строки. Если все ваши вызовы возвращают только (около) 2 строки, просто объявите это с добавлением ROWS 2. Также можно изменить план запроса для варианта VOLATILE (даже если STABLE в любом случае является правильным выбором).

person Erwin Brandstetter    schedule 30.01.2015
comment
Кажется, проблема не устранена: сканирование функций на get_web_events_by_userid (стоимость = 0,25..10,25 строк = 1000 ширина = 172) (фактическое время = 250263,587..250263,587 строк = 2 цикла = 1) Время планирования: 0,036 мс Время выполнения: 250263,612 мс Таблица auth_web_events имеет 100000000 записей, auth_user-›2 записи, customers-›1 запись - person Mmeyer; 30.01.2015
comment
@Mmeyer: Жаль! Затем, пожалуйста, рассмотрите рекомендации по дальнейшей отладке в последних комментариях к вопросу... и убедитесь, что оба вызова (func и query) возвращают идентичные результаты. - person Erwin Brandstetter; 30.01.2015
comment
спасибо !, с последним обновлением в вашем ответе функция работает нормально, я только что вставил план запроса в свой вопрос, в чем проблема? - person Mmeyer; 30.01.2015
comment
@Mmeyer: Не уверен. Кому ты рассказываешь. Вы можете начать с исходной функции и постепенно обновлять ее, чтобы увидеть, какое из моих обновлений помогло: 1. удалить недопустимый псевдоним. 2. переписать с явным синтаксисом соединения (не должно создавать различий). 3. Добавьте STABLE (вместо стандартного VOLATILE). 4. Измените условие WHERE на u.id = $1. 1 и 4 являются наиболее вероятными кандидатами. - person Erwin Brandstetter; 30.01.2015
comment
оператор STABLE сделал свое дело, я на самом деле не понимаю, что это значит. - person Mmeyer; 30.01.2015
comment
@Mmeyer: Приятно, что это работает. STABLE здесь является правильной настройкой и может принести пользу повторным вызовам в контексте более крупного запроса. Но это не должно иметь значения для вашего изолированного тестового примера. Я немного добавил к ответу. - person Erwin Brandstetter; 30.01.2015
comment
Что-то, что я нахожу странным, это план выполнения. В исходной версии он просто заявлял, что собирается оценить функцию. С вашим обновленным определением представленный план включает детали того, как оценивается запрос внутри функции. Есть идеи, почему? Может ли причина, по которой он не представляет более подробный план, заключаться в том, что планировщик не оптимизирует запрос внутри функции? - person jpmc26; 30.01.2015
comment
@ jpmc26: Вероятно, это просто вводящее в заблуждение представление ОП. План предназначен для самого запроса. Стандартный EXPLAIN не показывает планы запросов для того, что происходит внутри функций. Для этого вам нужно будет использовать автообъяснение. Я добавлю примечание к своему ответу. - person Erwin Brandstetter; 30.01.2015
comment
@ErwinBrandstetter Хм. После того, как я разместил свой комментарий, я смог воспроизвести это поведение EXPLAIN с помощью очень простой функции, переключаясь между VOLATILE и STABLE. Я сделал это в psql, и SHOW в нескольких параметрах конфигурации auto_explain дал нераспознанные ошибки. Так что я почти уверен, что auto_explain не был загружен или включен. Я на 9.3. Могу задать вопрос, если интересно. - person jpmc26; 31.01.2015
comment
Еще кое-что любопытное: я получаю rows=1000 (то же, что и OP), когда он не показывает внутренний план функции. Моя функция может вернуть максимум 5 строк, и она правильно оценивает 5 строк, когда показывает внутренний план. Я также вижу, что время выполнения увеличивается примерно в 3 раза по сравнению с выходом EXPLAIN ANALYZE. (Запрос выполняется настолько быстро, что я не уверен, что это важно.) Может ли планировщик вообще отказаться от вызова функции и просто встроить запрос как подзапрос? Это объяснило бы многое. - person jpmc26; 31.01.2015
comment
@ jpmc26: Звучит интересно, возможно, стоит задать вопрос. Но я уезжаю на выходные. - person Erwin Brandstetter; 31.01.2015
comment
@ErwinBrandstetter просто к вашему сведению, но я нашел этот вопрос и ответ, исследуя очень похожую проблему. У меня был запрос, который выполнялся примерно за 91 мс, а когда я поместил его в функцию, он подскочил до 4900 мс. Добавление STABLE сделало его похожим на необработанный SQL. - person David S; 10.05.2015

Вы повысите производительность, сделав этот запрос динамическим и используя plpgsql.

CREATE OR REPLACE FUNCTION get_web_events_by_userid(uid int) RETURNS TABLE(
    id int,
    time_stamp timestamp with time zone,
    description text,
    origin text,
    userlogin text,
    customer text,
    client_ip inet
     ) AS $$
BEGIN

RETURN QUERY EXECUTE
'SELECT 
  auth_web_events.id, 
  auth_web_events.time_stamp, 
  auth_web_events.description, 
  auth_web_events.origin,  
  auth_user.email AS user, 
  customers.name AS customer,
  auth_web_events.client_ip
FROM 
  public.auth_web_events, 
  public.auth_user, 
  public.customers
WHERE 
  auth_web_events.user_id_fk = auth_user.id AND
  auth_user.customer_id_fk = customers.id AND
  auth_web_events.user_id_fk = ' || uid ||
'ORDER BY
  auth_web_events.id DESC;'

END;
$$ LANGUAGE plpgsql;
person pwnyexpress    schedule 30.01.2015
comment
Хм, это действительно RETURN что-нибудь? Разве вам не пришлось бы использовать RETURN QUERY? - person jpmc26; 30.01.2015
comment
Думаю, это может повлиять на ваши результаты. Я не знаю, сможет ли он оптимизировать выполнение запроса, но, безусловно, вам следует еще раз убедиться, что производительность все еще лучше. - person jpmc26; 30.01.2015
comment
Я получаю: ОШИБКА: синтаксическая ошибка в или около SELECT LINE 13: SELECT ^ ********** Ошибка ********** ОШИБКА: синтаксическая ошибка в или около SELECT Состояние SQL: 42601 Персонаж: 268 - person Mmeyer; 30.01.2015
comment
Смотрите мою правку. Забыл, что выражение запроса должно быть строкой. - person pwnyexpress; 30.01.2015
comment
@ jpmc26 jpmc26 Поскольку запрос является динамическим, он будет генерировать новый план для каждого выполнения, а не общий. Это должно привести к повышению производительности. - person pwnyexpress; 30.01.2015
comment
@pwnyexpress См. этот вопрос и ответ, как указал rchang. Запрос должен быть перепланирован независимо от того, находится ли он в какой-либо функции или нет, начиная с версии 9.2 и выше. - person jpmc26; 30.01.2015
comment
Это вообще не должно быть необходимо. Простая функция SQL должна быть всем, что вам здесь нужно. PL/pgSQL может быть полезен без динамического SQL, поскольку он обрабатывает запрос как подготовленный оператор (повторно использует план запроса), но это совершенно не связано с рассматриваемой проблемой. Короче говоря: этот ответ вводит в заблуждение и в основном неверен. Кроме того, при использовании динамического SQL гораздо лучше передавать параметры значения с предложением USING вместо объединения текстовых представлений. - person Erwin Brandstetter; 30.01.2015