Re: Confirmation of bad query plan generated by 7.4

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

> "Jim C. Nasby" <jnasby@pervasive.com> 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      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461