"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
