Thread: Call for objections: simplify stable functions during estimation
Awhile back, there was some discussion about pre-folding now() and
related functions when the planner is trying to estimate selectivities.
This would allow reasonable plans to be made for cases likeWHERE moddate >= current_date - 10;
without having to indulge in any crude hacks with mislabeled wrapper
functions, such as you can find all too often in the archives :-(
I was a bit hesitant about it at the time because I wasn't sure of all
the implications; but I've looked the idea over again, and as far as I
can see it's reasonable to pre-fold *all* stable functions when deriving
statistical estimates. (One of the potential objections went away when
we started enforcing that stable functions don't have side-effects.)
The infrastructure for this is already there, because of Oliver Jowett's
previous work to teach eval_const_expressions() whether it's folding
the expression "for real" or just for estimation; it's basically a one
line change to treat stable functions differently in the two cases.
I know it's a bit late in the cycle, but I'd like to go ahead and make
this change for 8.0. Objections?
regards, tom lane
On Tue, 2004-11-09 at 16:28, Tom Lane wrote: > Awhile back, there was some discussion about pre-folding now() and > related functions when the planner is trying to estimate selectivities. > This would allow reasonable plans to be made for cases like > WHERE moddate >= current_date - 10; > without having to indulge in any crude hacks with mislabeled wrapper > functions, such as you can find all too often in the archives :-( > > I was a bit hesitant about it at the time because I wasn't sure of all > the implications; but I've looked the idea over again, and as far as I > can see it's reasonable to pre-fold *all* stable functions when deriving > statistical estimates. (One of the potential objections went away when > we started enforcing that stable functions don't have side-effects.) > > The infrastructure for this is already there, because of Oliver Jowett's > previous work to teach eval_const_expressions() whether it's folding > the expression "for real" or just for estimation; it's basically a one > line change to treat stable functions differently in the two cases. > > I know it's a bit late in the cycle, but I'd like to go ahead and make > this change for 8.0. Objections? None. IMHO Poor performance is a valid bug that must be addressed in the beta cycle. We've chased out most of the functional deficiencies, now its time to concentrate on the performance ones. -- Best Regards, Simon Riggs
On Tue, 9 Nov 2004, Tom Lane wrote: > Awhile back, there was some discussion about pre-folding now() and > related functions when the planner is trying to estimate selectivities. > This would allow reasonable plans to be made for cases like > WHERE moddate >= current_date - 10; > without having to indulge in any crude hacks with mislabeled wrapper > functions, such as you can find all too often in the archives :-( > > I was a bit hesitant about it at the time because I wasn't sure of all > the implications; but I've looked the idea over again, and as far as I > can see it's reasonable to pre-fold *all* stable functions when deriving > statistical estimates. (One of the potential objections went away when > we started enforcing that stable functions don't have side-effects.) > > The infrastructure for this is already there, because of Oliver Jowett's > previous work to teach eval_const_expressions() whether it's folding > the expression "for real" or just for estimation; it's basically a one > line change to treat stable functions differently in the two cases. > > I know it's a bit late in the cycle, but I'd like to go ahead and make > this change for 8.0. Objections? From a performance tuning standpoing, I can't argue against it ... go for it ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Tuesday 09 November 2004 11:28, Tom Lane wrote:
> Awhile back, there was some discussion about pre-folding now() and
> related functions when the planner is trying to estimate selectivities.
> This would allow reasonable plans to be made for cases like
> WHERE moddate >= current_date - 10;
> without having to indulge in any crude hacks with mislabeled wrapper
> functions, such as you can find all too often in the archives :-(
>
> I was a bit hesitant about it at the time because I wasn't sure of all
> the implications; but I've looked the idea over again, and as far as I
> can see it's reasonable to pre-fold *all* stable functions when deriving
> statistical estimates. (One of the potential objections went away when
> we started enforcing that stable functions don't have side-effects.)
>
Since we know people will be calling volatile functions inside stable
functions (see thread from last week if you need a refresher as to why) is
there any serious negative side-effect in those cases?
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Tuesday 09 November 2004 11:28, Tom Lane wrote:
>> (One of the potential objections went away when
>> we started enforcing that stable functions don't have side-effects.)
> Since we know people will be calling volatile functions inside stable
> functions (see thread from last week if you need a refresher as to why) is
> there any serious negative side-effect in those cases?
If you are making an end-run around the rule, IMHO it's up to you to make
sure that the behavior of the function is sane. In practice, the
planner is only going to be estimating values for functions that appear
in WHERE/GROUP BY/HAVING clauses, and anyone who puts a function with
real side-effects in such places is in deep trouble anyway.
The real bottom line here is that it's better to take the current value
of the function as the planner estimate than to fall back to completely
default selectivity estimates. You can doubtless invent scenarios where
this is wrong, but they are far outweighed by cases where it is right.
regards, tom lane