Запрос Postgres очень медленный с current_date::date вместо жестко заданной даты

У меня есть довольно длинный и сложный SQL-запрос, который выполняется для PostgreSQL 8.3. Часть запроса включает фильтрацию диапазона дат, заканчивающихся сегодняшним днем, например:

where ...
  and sp1.price_date between current_date::date - '1 year'::interval and current_date::date
  and sp4.price_date between current_date::date - '2 weeks'::interval and current_date::date
  and sp5.price_date = (select sp6.price_date 
                          from stock_prices sp6 
                         where sp6.stock_id = s.stock_id 
                           and sp6.price_date < current_date::date 
                      order by sp6.price_date desc 
                         limit 1)
  ...

Этот запрос выполняется почти 5 минут (в первый раз) и около 1,5 минут во второй раз. Судя по выходным данным EXPLAIN ANALYZE, проблема заключается в current_date. Поэтому я попытался заменить его жестко заданной датой, например:

where ...
  and sp1.price_date between '2009-09-30'::date - '1 year'::interval and '2009-09-30'::date
  and sp4.price_date between '2009-09-30'::date - '2 weeks'::interval and '2009-09-30'::date
  and sp5.price_date = (select sp6.price_date 
                          from stock_prices sp6 
                         where sp6.stock_id = s.stock_id 
                           and sp6.price_date < '2009-09-30'::date 
                      order by sp6.price_date desc 
                         limit 1)
  ...

Затем запрос выполнялся за полсекунды! Это здорово, за исключением того, что дата встречается в запросе всего в 10 местах, и, конечно же, я не хочу, чтобы пользователю приходилось вручную менять ее в 10 местах. В MS SQL Server я бы просто объявил переменную со значением текущей даты и использовал ее, но очевидно это невозможно в простом SQL в Postgres.

Что я могу сделать, чтобы этот запрос выполнялся быстро, автоматически используя текущую дату?


postgresql sql
person EMP    schedule 29.09.2009    source источник
comment
current_date::date Очень странно, зачем вам часть ::date, если она уже имеет тип date? Более того, если вы добавляете/вычитаете интервалы из даты, результат все равно будет отметкой времени!   -  person Jamol    schedule 30.09.2009

Ответы (2)


arrow_upward
2
arrow_downward

Прежде всего, опубликуйте EXPLAIN ANALYZE для обоих вариантов, чтобы мы можно увидеть. Первый шаг к выяснению того, почему один медленнее другого. Также может быть полезно увидеть весь запрос.

Первый вариант должен быть оптимизируемым.

Что касается того, чтобы ваш пользователь не менял ваш запрос в нескольких местах, рассмотрите возможность написания сохраненного процедура или, если/когда ваш первый вариант оптимизирован, просмотреть.

Изменить: заметил, что ваш current__date - '...'::interval вернет метку времени без часового пояса. Я предполагаю, что вместо этого вы хотите использовать дату: (current_date - '2 days'::interval)::date

person tommym    schedule 29.09.2009
comment
Извините, я сделал ОБЪЯСНЕНИЕ, АНАЛИЗ, а не ОБЪЯСНЕНИЕ (отредактировано). Я не уверен, как еще его оптимизировать. Я попытался написать функцию, которая возвращает SETOF RECORD для выполнения запроса и использования параметра в этой функции, но функция не возвращалась через 10 минут, после чего я ее отменил. - person EMP; 30.09.2009
comment
@Evgeny: Запуск объяснения анализа сам по себе не помогает. Вы должны прочитать его вывод и найти проблему. - person ; 30.09.2009
comment
@Evgeny: Возможно, я немного неясно выразился. Пожалуйста, опубликуйте результаты анализа объяснения здесь. - person tommym; 30.09.2009

arrow_upward
1
arrow_downward

РЕДАКТИРОВАТЬ: было протестировано следующее, но оно работало даже медленнее, чем исходный запрос!. Урок по этому поводу, возможно, заключается в том, что снижение производительности связано со всем приведением типов (::date, ::interval и т. д.). Возможно, эти явные приведения можно как-то заменить чем-то другим, а также предварительно вычислить некоторые выражения, такие как 'D.RightNow::date - '1 year'::interval'.

-- исходный ответ--
Вы можете вставить текущую дату в пустую таблицу и присоединиться к этой таблице...

Другими словами, если предположить, что такая таблица создана и названа tblNow, запрос(ы) с фильтрами, связанными с датой, может выглядеть примерно так:

UPDATE tblNow SET RightNow = TIMEOFDAY();  
-- note: above could use CURRENT_DATE  or whatever date function matches the
-- semantics of the date fields in other tables.

-- and now the original query can become

from ...
join tblNow AS D ON 1=1 -- added join
                   -- then all current_date::date below changed to D.RightNow

where ...
  and sp1.price_date between D.RightNow::date - '1 year'::interval and D.RightNow::date
  and sp4.price_date between D.RightNow::date - '2 weeks'::interval and D.RightNow::date
  and sp5.price_date = (select sp6.price_date 
                          from stock_prices sp6 
                         where sp6.stock_id = s.stock_id 
                           and sp6.price_date < D.RightNow::date 
                      order by sp6.price_date desc 
                         limit 1)
  ...

Эффективно требует никакого редактирования запроса каждый раз, когда мы хотим запустить его в текущий момент. Я не знаком с postgreSQL, но этот подход был бы естественным обходным путем для любого ограничения на использование переменных в операторах SELECT.

person mjv    schedule 29.09.2009
comment
Как это поможет ему? Вместо того, чтобы изменить запрос, он должен добавить/обновить строки в отдельной таблице? - person tommym; 30.09.2009
comment
@etlerant: в OP упоминается необходимость изменить дату в более чем 10 местах в запросе (запросах), этот подход позволит 1) указать одно местоположение и 2) вообще не вносить изменения в запрос, если запрос UPDATE с использованием timeofday () предшествует рабочим запросам. - person mjv; 30.09.2009
comment
Спасибо, я попробовал это, но это не помогло - запрос выполнялся в течение 10 минут, прежде чем я сдался и отменил его. - person EMP; 30.09.2009
comment
@Евгений. Извини, что повел тебя по ложному пути. Я соответствующим образом отредактировал свой ответ, чтобы записать это понимание (о том, что он такой медленный). Возможно, медлительность связана с многочисленными приведениями типов, а также, возможно, мы можем помочь, сохранив в единственной записи tblNow дополнительные поля, в которых некоторые выражения даты могут быть предварительно вычислены. - person mjv; 30.09.2009