使用current_date :: date而非硬编码日期的Postgres查询非常慢 [英] Postgres query is very slow with current_date::date instead of hardcoded date
问题描述
我有相当长且复杂的SQL查询，它针对PostgreSQL 8.3运行。查询的一部分涉及过滤以今天结束的日期范围，例如：
I have fairly long and complex SQL query that is run against PostgreSQL 8.3. Part of the query involves filtering on a range of dates ending with today, like this:
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 是问题所在。所以我尝试用一个硬编码的日期替换它，像这样：
This query takes almost 5 minutes to run (the first time) and about 1.5 minutes the second time. From looking at the EXPLAIN ANALYZE output it seems that current_date is the problem. So I tried replacing it with a hardcoded date, like this:
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中，我只需要声明一个具有当前日期值的变量并使用它，但是显然，在Postgres的普通SQL中是不可能的。
The query then ran in half a second! That's great, except that the date occurs in a total of 10 places in the query and, of course, I don't want the user to have to manually change it in 10 places. In MS SQL Server I would simply declare a variable with the value of the current date and use that, but apparently that's not possible in plain SQL in Postgres.
我可以做些什么来使此查询快速运行，同时自动使用当前日期？
What can I do to make this query run fast while automatically using the current date?
推荐答案
首先，发布 EXPLAIN ANALYZE 对这两个变体的解释都可以看到。弄清楚我们为什么一个人比另一个人慢的第一步。
First of all, post EXPLAIN ANALYZE on both variants so we can see. First step in figuring our why one is slower than the other. Might be useful to see the entire query as well.
第一个变量应该是可优化的。
The first variant should be optimizable.
关于没有让您的用户在多个地方更改查询，请考虑编写存储过程，或者如果/当您优化了第一个变量时，则视图。
As to not having your user change your query at multiple places consider writing a stored procedure, or if/when your first variant is optimized, a view.
编辑：注意，您当前的__date-'...':: interval会返回没有时区的时间戳。我假设您想强制转换为日期：（current_date-‘2 week’:: interval）:: date
Noticed that your current__date - '...'::interval would return a timestamp w/o timezone. I assume you want to cast to date instead: (current_date - '2 weeks'::interval)::date
这篇关于使用current_date :: date而非硬编码日期的Postgres查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
