Thread: dynamic-static date

I have something like this:

billy=# EXPLAIN SELECT * from kursy where id_trasy=1 and
data_kursu=date('2001-12-12');
NOTICE:  QUERY PLAN:
Index Scan using pp on kursy  (cost=0.00..51.55 rows=1 width=18)

billy=# EXPLAIN SELECT * from kursy where id_trasy=1
and data_kursu='2001-12-12';
NOTICE:  QUERY PLAN:
Index Scan using pp on kursy  (cost=0.00..2.02 rows=1 width=18)

Why the first expression is 25 times slower?
I suppose, that planner thinks, that date('2001-12-12') is a dynamic
variable - is it true? I found this problem when i had to add date and
integer. Little "iscachable" function helped me, but I still don't know
why it happened.

CREATE FUNCTION date_sum(date,integer) returns date AS'
BEGIN
  return $1+$2;
END;
'LANGUAGE 'plpgsql' WITH (iscachable);

Tomasz Myrta <jasiek@lamer.pl> writes:
> Why the first expression is 25 times slower?

Hard to say, when you haven't shown us the schema.  (Column datatypes,
definitions of available indexes, etc are all critical information for
this sort of question.)

            regards, tom lane

Tom Lane wrote:
>
> Tomasz Myrta <jasiek@lamer.pl> writes:
> > Why the first expression is 25 times slower?
>
> Hard to say, when you haven't shown us the schema.  (Column datatypes,
> definitions of available indexes, etc are all critical information for
> this sort of question.)
OK
Don't panic with names, They are polish ;-)

1. TABLES
create table TRASY(
  id_trasy              integer  not null PRIMARY KEY,
  del                   date     default '9999-12-31',
  nazwa                 varchar  (80)
);

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
);

2. INDEXES

 trasy     |  CREATE UNIQUE INDEX trasy_pkey ON trasy USING btree
(id_trasy int4_ops)
 kursy     |  CREATE UNIQUE INDEX kursy_pkey ON kursy USING btree
(id_kursu int4_ops)
 kursy     |  CREATE INDEX ind_kurs_ ON kursy USING btree (id_trasy
int4_ops, data_kursu date_ops)

3. TEST

This time kursy has less rows:

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)

I think that's all

Tomek

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

...
> I think we have agreed that 'current' is a Bad Idea and should be
> eliminated from the date/time datatypes...

I've started purging it from the timestamp code I'm working on for 7.2.
Should be gone by the start of beta...
                    - Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:
>> I think we have agreed that 'current' is a Bad Idea and should be
>> eliminated from the date/time datatypes...

> I've started purging it from the timestamp code I'm working on for 7.2.

Oh good.  Let's not forget to review the pg_proc entries after that
happens, to see which ones can safely be marked cachable.
        regards, tom lane