Re: dynamic-static date once again

Tomasz Myrta <jasiek(at)lamer(dot)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