Re: On using "date 'XXX' + interval 'XXX'" vs "date 'XXX'"

Jon Lapham <lapham(at)extracta(dot)com(dot)br> writes:
> In rewriting some queries I noticed a huge performance penalty when
> using a "date + interval" summation in the SELECT statement, versus a
> single simple "date". It is almost as though the "date + interval" is
> being calculated for each row...

Try coercing the sum result back to a date.

It's a little easier to see what's happening in current sources:

regression=# create table sample_tracker (initdate date primary key);

regression=# set enable_seqscan TO 0;
SET
regression=# explain select count(*) from sample_tracker where
regression-# initdate>=date '2002-02-01';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Aggregate (cost=46.33..46.33 rows=1 width=0)
-> Index Scan using sample_tracker_pkey on sample_tracker (cost=0.00..45.50 rows=333 width=0)
Index Cond: (initdate >= '2002-02-01'::date)
(3 rows)

regression=# explain select count(*) from sample_tracker where
regression-# initdate>=date '2002-01-01' + interval '1 month';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=100000025.83..100000025.83 rows=1 width=0)
-> Seq Scan on sample_tracker (cost=100000000.00..100000025.00 rows=333 width=0)
Filter: ("timestamp"(initdate) >= '2002-02-01 00:00:00'::timestamp without time zone)
(3 rows)

regression=#

Writing date(date '2002-01-01' + interval '1 month') gets me back to
the first plan.

regards, tom lane