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

Jon Lapham <lapham@extracta.com.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