To: "Jim C. Nasby" <jnasby@pervasive.com>
cc: Shaun Thomas <sthomas@leapfrogonline.com>,
 pgsql-performance@postgresql.org
Subject: Re: Confirmation of bad query plan generated by 7.4 
In-reply-to: <20060613211347.GB34196@pervasive.com> 
References: <9119.1150222168@sss.pgh.pa.us>
	<448EDFC4.8F27.00A9.0@leapfrogonline.com>
	<20060613211347.GB34196@pervasive.com>
Comments: In-reply-to "Jim C. Nasby" <jnasby@pervasive.com>
	message dated "Tue, 13 Jun 2006 16:13:47 -0500"
Date: Tue, 13 Jun 2006 18:04:42 -0400
Message-ID: <10918.1150236282@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Also, I'm not sure that the behavior is entirely changed, either. On a
> 8.1.4 database I'm still seeing a difference between now() - interval
> and a hard-coded date.

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:

-- create and populate 1000-row table:

regression=# create table t1 (f1 timestamptz);
CREATE TABLE
regression=# insert into t1 select now() - x * interval '1 day' from generate_series(1,1000) x;
INSERT 0 1000

-- default estimate is pretty awful:

regression=# explain select * from t1 where f1 > now();
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t1  (cost=0.00..39.10 rows=647 width=8)
   Filter: (f1 > now())
(2 rows)

regression=# vacuum t1;
VACUUM

-- now the planner at least knows how many rows in the table with some
-- accuracy, but with no stats it's still falling back on a default
-- selectivity estimate:

regression=# explain select * from t1 where f1 > now();
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t1  (cost=0.00..21.00 rows=333 width=8)
   Filter: (f1 > now())
(2 rows)

-- and the default doesn't really care what the comparison value is:

regression=# explain select * from t1 where f1 > now() - interval '10 days';
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t1  (cost=0.00..23.50 rows=333 width=8)
   Filter: (f1 > (now() - '10 days'::interval))
(2 rows)

-- but let's give it some stats:

regression=# vacuum analyze t1;
VACUUM

-- and things get better:

regression=# explain select * from t1 where f1 > now() - interval '10 days';
                    QUERY PLAN                     
---------------------------------------------------
 Seq Scan on t1  (cost=0.00..23.50 rows=9 width=8)
   Filter: (f1 > (now() - '10 days'::interval))
(2 rows)

7.4 would still be saying "rows=333" in the last case, because it's
falling back on DEFAULT_INEQ_SEL whenever the comparison value isn't
strictly constant.

			regards, tom lane