On Jun 13, 2006, at 8:50 PM, Tom Lane wrote:

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
On Tue, Jun 13, 2006 at 06:04:42PM -0400, Tom Lane wrote:
It'd depend on the context, possibly, but it's easy to show that the
current planner does fold "now() - interval_constant" when making
estimates.  Simple example:

Turns out the difference is between feeding a date vs a timestamp into the query... I would have thought that since date is a date that the WHERE clause
would be casted to a date if it was a timestamptz, but I guess not...

Hmm ... worksforme.  Could you provide a complete test case?

decibel=# create table date_test(d date not null, i int not null);
CREATE TABLE
decibel=# insert into date_test select now()-x*'1 day'::interval, i from generate_series(0,3000) x, generate_series(1,100000) i;
INSERT 0 300100000
decibel=# analyze verbose date_test;
INFO:  analyzing "decibel.date_test"
INFO: "date_test": scanned 30000 of 1622163 pages, containing 5550000 live rows and 0 dead rows; 30000 rows in sample, 300100155 estimated total rows
ANALYZE
decibel=# explain select * from date_test where d >= now()-'15 days'::interval;
                             QUERY PLAN
---------------------------------------------------------------------
Seq Scan on date_test  (cost=0.00..6873915.80 rows=1228164 width=8)
   Filter: (d >= (now() - '15 days'::interval))
(2 rows)

decibel=# explain select * from date_test where d >= (now()-'15 days'::interval)::date;
                             QUERY PLAN
---------------------------------------------------------------------
Seq Scan on date_test  (cost=0.00..7624166.20 rows=1306467 width=8)
   Filter: (d >= ((now() - '15 days'::interval))::date)
(2 rows)

decibel=# select version();
                                             version
------------------------------------------------------------------------ ------------------------- PostgreSQL 8.1.4 on amd64-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518
(1 row)

decibel=#

--
Jim C. Nasby, Sr. Engineering Consultant      [EMAIL PROTECTED]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to