Re: dynamic-static date once again

Tomasz Myrta <jasiek@lamer.pl> writes:
> create table KURSY(
>   id_kursu              integer  not null PRIMARY KEY,
>   id_trasy              integer  not null references TRASY,
>   data_kursu            date     not null,
>   limit_miejsc          smallint not null
> );
> CREATE INDEX ind_kurs_ ON kursy USING btree (id_trasy
> int4_ops, data_kursu date_ops)

> saik=# EXPLAIN SELECT * from kursy where id_trasy=1 and
> saik-# data_kursu=date('2001-12-12');
> NOTICE:  QUERY PLAN:

> Index Scan using ind_kurs_ on kursy  (cost=0.00..8.19 rows=1 width=14)

> EXPLAIN
> saik=# EXPLAIN SELECT * from kursy where id_trasy=1
> saik-# and data_kursu='2001-12-12';
> NOTICE:  QUERY PLAN:

> Index Scan using ind_kurs_ on kursy  (cost=0.00..2.02 rows=1 width=14)

Okay, the reason for the difference in cost estimate (which you should
never mistake for reality, btw ;-)) is that the second example is using
both columns of the index, whereas the first example is using only the
first index column --- the restriction data_kursu=date('2001-12-12')
will be checked explicitly at each row, not implemented as an indexscan
bound.

The cause is precisely that date() is considered a noncachable function,
and so the planner doesn't think that date('2001-12-12') is a constant.
And the reason for that is that the date/time datatypes have a construct
called 'current', which is indeed not a constant.

I think we have agreed that 'current' is a Bad Idea and should be
eliminated from the date/time datatypes --- but until that happens,
forcing the constant to be considered a constant is your only
alternative.  Write
    date '2001-12-12'
or
    '2001-12-12'::date
instead of writing date().

            regards, tom lane