Jamie Tufnell <diesql@googlemail.com> writes:
> AFAIK current_date is standard and 'now' and 'today', etc are not...
> so that's one reason to continuing using current_date. However, I
> wonder why 'today' and current_date don't generate the same query
> plan?
'today'::date is a special string that is recognized by the date type's
input routine, but what it produces is a constant, for instance
'2009-02-05'::date if I executed it today. current_date is a function
that produces the current date whenever it is executed. Consider
for instance
regression=# create view v as select current_date as d1, 'today'::date as d2;
CREATE VIEW
regression=# select * from v; d1 | d2
------------+------------2009-02-05 | 2009-02-05
(1 row)
regression=# \d v View "public.v"Column | Type | Modifiers
--------+------+-----------d1 | date | d2 | date |
View definition:SELECT 'now'::text::date AS d1, '2009-02-05'::date AS d2;
As the view definition printout suggests, tomorrow this view will produce
d1 | d2
------------+------------2009-02-06 | 2009-02-05
because the constant isn't going to change.
As you can see from both this example and your own, our current
implementation of current_date is ('now'::text)::date. (Which
sucks, we ought to hide it better ;-)) What that actually means
is you have a constant of type text containing the string 'now',
and when the expression is executed there's a *run time* cast to
type date, causing the date input converter to get invoked at
that time. So it'll still work tomorrow.
Of course, the cost of the runtime interpretation of the string
is what accounts for your speed difference.
regards, tom lane