Why is there so different "Total runtime" for ('now'::text)::date) and
'2005-04-06'::date ?

I think both is constant during execution.


CREATE TABLE test (
_time timestamp with time zone,
_platform character(5),
_tld character(5)
) WITHOUT OIDS;


explain analyze SELECT count(*) from test where
_time::date=CURRENT_DATE;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=0.01..0.01 rows=1 width=0) (actual time=0.026..0.029
rows=1 loops=1)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=0) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((_time)::date = ('now'::text)::date)
Total runtime: 24.034 ms
^^^^^^^^


explain analyze SELECT count(*) from test where
_time::date='2005-04-06'::date;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=0.01..0.01 rows=1 width=0) (actual time=0.015..0.018
rows=1 loops=1)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=0) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((_time)::date = '2005-04-06'::date)
Total runtime: 0.065 ms
^^^^^^^^

Karel

--
Karel Zak <zakkr@zf.jcu.cz>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Karel Zak <zakkr@zf.jcu.cz> writes:
> Why is there so different "Total runtime" for ('now'::text)::date) and
> '2005-04-06'::date ?

> I think both is constant during execution.

Not at all. text_date() is not immutable so it has to be applied during
execution. The other one is just a constant.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend