Обсуждение: 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