From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Shaun Thomas <sthomas@leapfrogonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Confirmation of bad query plan generated by 7.4
Date: Tue, 13 Jun 2006 18:04:42 -0400
Message-ID: <10918.1150236282@sss.pgh.pa.us> (raw)
In-Reply-To: <20060613211347.GB34196@pervasive.com>
References: <9119.1150222168@sss.pgh.pa.us>
<448EDFC4.8F27.00A9.0@leapfrogonline.com>
<20060613211347.GB34196@pervasive.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jnasby@pervasive.com, sthomas@leapfrogonline.com
Subject: Re: Confirmation of bad query plan generated by 7.4
In-Reply-To: <10918.1150236282@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox