On Thu, Jul 9, 2009 at 5:38 AM, Noah Mischn...@leadboat.com wrote:
z
Describing in those terms illuminates much. While the concepts do suggest 2^N
worst-case planning cost, my artificial test case showed a rigid 4^N pattern;
what could explain that?
Isn`t that just so that the planner has to
Hi Tom,
On Saturday 11 July 2009 20:33:14 Tom Lane wrote:
Andres Freund and...@anarazel.de writes:
I just realized doing it in a naive way (in geqo()) causes the state to
be reset multiple times during one query- at every invocation of
make_rel_from_joinlist.
Does anybody see a problem
Andres Freund and...@anarazel.de writes:
On Saturday 11 July 2009 20:33:14 Tom Lane wrote:
This ties into something I was thinking about earlier: the planner is
potentially recursive (eg, it might call a user-defined function that
contains a plannable query), and it'd probably be a good idea
On Sunday 12 July 2009 16:44:59 Tom Lane wrote:
Andres Freund and...@anarazel.de writes:
On Saturday 11 July 2009 20:33:14 Tom Lane wrote:
This ties into something I was thinking about earlier: the planner is
potentially recursive (eg, it might call a user-defined function that
contains a
Andres Freund and...@anarazel.de writes:
Has anybody tried Geqo without ERX in recent time?
No, I don't think so. Anything that's ifdef'd out at the moment has
been that way for quite a few years, and so is likely broken anyhow :-(
regards, tom lane
--
Sent via
On Wednesday 08 July 2009 23:46:02 Tom Lane wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
For a moment it seemed logical to suggest a session GUC for the seed,
so if you got a bad plan you could keep rolling the dice until you got
one you liked; but my right-brain kept sending
Andres Freund and...@anarazel.de writes:
The only question I have is, whether random_r or similar is available on
enough platforms... Has anybody an idea about this?
On most unixoid system one could just wrap erand48() if random_r is not
available.
Windows?
random_r() isn't in the Single
Hi,
On Saturday 11 July 2009 18:23:59 Tom Lane wrote:
Andres Freund and...@anarazel.de writes:
The only question I have is, whether random_r or similar is available on
enough platforms... Has anybody an idea about this?
On most unixoid system one could just wrap erand48() if random_r is
Andres Freund and...@anarazel.de writes:
On Saturday 11 July 2009 18:23:59 Tom Lane wrote:
So far as I can find in a quick google search, neither of these families
of functions exist on Windows :-(. So I think maybe the best approach
is the second one --- we could implement a port/ module
On Sat, Jul 11, 2009 at 12:23:59PM -0400, Tom Lane wrote:
Andres Freund and...@anarazel.de writes:
The only question I have is, whether random_r or similar is available on
enough platforms... Has anybody an idea about this?
On most unixoid system one could just wrap erand48() if random_r
On Saturday 11 July 2009 18:23:59 Tom Lane wrote:
On reflection I think the best user API is probably a geqo_seed GUC in
the range 0 to 1, and have GEQO always reset its seed to that value at
start of a planning cycle. This ensures plan stability, and if you need
to experiment with
Andres Freund and...@anarazel.de writes:
I just realized doing it in a naive way (in geqo()) causes the state to be
reset multiple times during one query- at every invocation of
make_rel_from_joinlist.
Does anybody see a problem with that?
I think that's probably what we want. Otherwise,
On Wed, Jul 8, 2009 at 4:57 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Well, the reason I'm not voting for #3 is that it looks like a lot of
work to implement something that would basically be a planner hint,
which I'm generally against; furthermore, it's a hint that there's been
no demand for.
Hi,
Le 10 juil. 09 à 17:22, Robert Haas a écrit :
I took a look at this and it seems that #3 can be implemented with
essentially no additional code (the handful of lines I added where
more than balanced out by some simplifications in ruleutils.c). Of
course you still don't have to like it.
Robert Haas robertmh...@gmail.com writes:
I took a look at this and it seems that #3 can be implemented with
essentially no additional code (the handful of lines I added where
more than balanced out by some simplifications in ruleutils.c). Of
course you still don't have to like it. :-)
On Jul 10, 2009, at 11:48 AM, Dimitri Fontaine dfonta...@hi-
media.com wrote:
Hi,
Le 10 juil. 09 à 17:22, Robert Haas a écrit :
I took a look at this and it seems that #3 can be implemented with
essentially no additional code (the handful of lines I added where
more than balanced out by some
Robert Haas robertmh...@gmail.com wrote:
At any rate the particular choice of keyword seems rather
insignificant; I picked it because it was already a keyword and
seemed vaguely appropriate, but it could easily be changed.
Actually, if we were going to add fine-grained optimizer hints
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Actually, if we were going to add fine-grained optimizer hints for
this (which I'm not at all convinced is a good idea), I'd be tempted
to go with what I saw a few years ago in SAP-DB (later rebranded as
MySQL Max-DB): treat parentheses around
Tom Lane t...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
treat parentheses around JOIN operations as optimizer hints.
That's a *truly* horrid idea, as sometimes you need them simply to
get the precedence correct.
You do, but it's been pretty rare in my
On Jul 10, 2009, at 12:06 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
I took a look at this and it seems that #3 can be implemented with
essentially no additional code (the handful of lines I added where
more than balanced out by some simplifications in
On Fri, Jul 10, 2009 at 10:22 AM, Robert Haasrobertmh...@gmail.com wrote:
I took a look at this and it seems that #3 can be implemented with
essentially no additional code (the handful of lines I added where
more than balanced out by some simplifications in ruleutils.c). Of
course you still
Kevin Grittner kevin.gritt...@wicourts.gov writes:
You do, but it's been pretty rare in my experience, and we're
considering alternatives which give a lot less flexibility that this.
I dunno about considering. We've already wasted vastly more time on
this than it's worth. AFAIR there has
Tom Lane wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
You do, but it's been pretty rare in my experience, and we're
considering alternatives which give a lot less flexibility that this.
I dunno about considering. We've already wasted vastly more time on
this than it's worth.
On Fri, Jul 10, 2009 at 2:44 PM, Jaime
Casanovajcasa...@systemguards.com.ec wrote:
On Fri, Jul 10, 2009 at 10:22 AM, Robert Haasrobertmh...@gmail.com wrote:
I took a look at this and it seems that #3 can be implemented with
essentially no additional code (the handful of lines I added where
On Fri, Jul 10, 2009 at 2:48 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
You do, but it's been pretty rare in my experience, and we're
considering alternatives which give a lot less flexibility that this.
I dunno about considering. We've already
On Jul 8, 2009, at 8:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
That was my first reaction too, but now I'm wondering whether we
shouldn't just do #1. #2 is a planner hint, too, just not a very
good
one. If, as you suggest, it isn't actually
Hi,
Robert Haas robertmh...@gmail.com writes:
On Jul 8, 2009, at 8:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Now, your answer will probably be that we should provide some better
mechanism for re-using a previously identified plan structure. No
doubt that would be ideal, but the amount of
On Wed, Jul 8, 2009 at 8:26 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
That was my first reaction too, but now I'm wondering whether we
shouldn't just do #1. #2 is a planner hint, too, just not a very good
one. If, as you suggest, it isn't actually
On Wed, Jul 08, 2009 at 05:23:16PM -0400, Tom Lane wrote:
Noah Misch n...@leadboat.com writes:
The expontential factor seems smaller for real queries. I have a query of
sixteen joins that takes 71s to plan deterministically; it looks like this:
SELECT 1 FROM fact JOIN dim0 ... JOIN dim6
Robert Haas robertmh...@gmail.com wrote:
If, as you suggest, it isn't actually useful, then why keep it at
all?
I was imagining that someone who has a query which is taking a long
time to run, and asserts that it would run faster if only the
optimizer would arrange the joins a certain way,
Resending to list due to failure:
451 4.3.5 Server configuration problem
Joshua Tolley eggyk...@gmail.com wrote:
Entire stored plans, or predetermined seeds for GEQO's random number
generator all boil down to saying, I want you to use this plan
henceforth and forever.
A predetermined seed
On Thursday 09 July 2009 17:37:41 Kevin Grittner wrote:
Resending to list due to failure:
451 4.3.5 Server configuration problem
Joshua Tolley eggyk...@gmail.com wrote:
Entire stored plans, or predetermined seeds for GEQO's random number
generator all boil down to saying, I want you to use
On Wed, Jul 08, 2009 at 10:28:02AM -0500, Robert Haas wrote:
On Jul 8, 2009, at 8:43 AM, Noah Misch n...@leadboat.com wrote:
The expontential factor seems smaller for real queries. I have a query of
sixteen joins that takes 71s to plan deterministically; it looks like this:
SELECT 1 FROM
Tom Lane wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
I guess the question is whether there is anyone who has had a contrary
experience. (There must have been some benchmarks to justify adding
geqo at some point?)
The CVS history shows that geqo was integrated on 1997-02-19,
On Tue, Jul 07, 2009 at 09:31:14AM -0500, Kevin Grittner wrote:
I don't remember any clear resolution to the wild variations in plan
time mentioned here:
http://archives.postgresql.org/pgsql-hackers/2009-06/msg00743.php
I think it would be prudent to try to figure out why small changes
Hi,
When I was first familiarizing myself with PostgreSQL, I took a
walk through its documentation on GECO and similar processes in
the literature. One big advantage of GECO is that you can trade
off planning time for plan optimization. I do agree that it should
be updated, but there were
Robert Haas robertmh...@gmail.com writes:
On Tue, Jul 7, 2009 at 6:33 PM, Tom Lanet...@sss.pgh.pa.us wrote:
It's pretty much all-or-nothing now: the GUC does not give you any sort
of useful control over *which* joins are reorderable.
Yes. So the way I see it, the options are:
1. We can
Tom Lane t...@sss.pgh.pa.us wrote:
It occurs to me that one way to make GEQO less scary would be to
take out the nondeterminism by resetting its random number generator
for each query. You might get a good plan or an awful one, but at
least it'd be the same one each time. DBAs like
On Wed, Jul 08, 2009 at 04:13:11PM -0500, Kevin Grittner wrote:
Tom Lane t...@sss.pgh.pa.us wrote:
It occurs to me that one way to make GEQO less scary would be to
take out the nondeterminism by resetting its random number generator
for each query. You might get a good plan or an awful
Noah Misch n...@leadboat.com writes:
With joins between statistically indistinguishable columns, I see planning
times
change by a factor of ~4 for each join added or removed (postgres 8.3).
Varying
join_collapse_limit in the neighborhood of the actual number of joins has a
similar effect.
Kevin Grittner kevin.gritt...@wicourts.gov writes:
For a moment it seemed logical to suggest a session GUC for the seed,
so if you got a bad plan you could keep rolling the dice until you got
one you liked; but my right-brain kept sending shivers down my spine
to suggest just how uncomfortable
On Wed, Jul 08, 2009 at 05:46:02PM -0400, Tom Lane wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
For a moment it seemed logical to suggest a session GUC for the seed,
so if you got a bad plan you could keep rolling the dice until you got
one you liked; but my right-brain kept
On Jul 8, 2009, at 3:57 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Tue, Jul 7, 2009 at 6:33 PM, Tom Lanet...@sss.pgh.pa.us wrote:
It's pretty much all-or-nothing now: the GUC does not give you any
sort
of useful control over *which* joins are
Robert Haas robertmh...@gmail.com writes:
That was my first reaction too, but now I'm wondering whether we
shouldn't just do #1. #2 is a planner hint, too, just not a very good
one. If, as you suggest, it isn't actually useful, then why keep it
at all? (On the other hand, if someone
On Wed, Jul 08, 2009 at 09:26:35PM -0400, Tom Lane wrote:
Robert Haas robertmh...@gmail.com writes:
That was my first reaction too, but now I'm wondering whether we
shouldn't just do #1. #2 is a planner hint, too, just not a very good
one. If, as you suggest, it isn't actually useful,
Joshua Tolley eggyk...@gmail.com writes:
This sounds like planner hints to me. The argument against hinting, AIUI, is
that although the plan you've guaranteed via hints may be a good one today,
when the data change a bit your carefully crafted plan happens to become a bad
one, but you're no
Noah Misch n...@leadboat.com writes:
Describing in those terms illuminates much. While the concepts do suggest 2^N
worst-case planning cost, my artificial test case showed a rigid 4^N pattern;
what could explain that?
Well, the point of the 2^N concept is just that adding one more relation
Robert Haas robertmh...@gmail.com wrote:
I'm interested in hearing from anyone who has practical experience
with tuning these variables, or any ideas on what we should test to
get a better idea as to how to set them.
I don't remember any clear resolution to the wild variations in plan
time
On Jul 7, 2009, at 9:31 AM, Kevin Grittner kevin.gritt...@wicourts.gov
wrote:
Robert Haas robertmh...@gmail.com wrote:
I'm interested in hearing from anyone who has practical experience
with tuning these variables, or any ideas on what we should test to
get a better idea as to how to set
Hi Kevin, Hi all,
On Tuesday 07 July 2009 16:31:14 Kevin Grittner wrote:
Robert Haas robertmh...@gmail.com wrote:
I'm interested in hearing from anyone who has practical experience
with tuning these variables, or any ideas on what we should test to
get a better idea as to how to set them.
Andres Freund and...@anarazel.de writes:
I cannot reasonably plan some queries with join_collapse_limit set to 20. At
least not without setting the geqo limit very low and a geqo_effort to a low
value.
So I would definitely not agree that removing j_c_l is a good idea.
Can you show some
On Tuesday 07 July 2009 17:40:50 Tom Lane wrote:
Andres Freund and...@anarazel.de writes:
I cannot reasonably plan some queries with join_collapse_limit set to 20.
At least not without setting the geqo limit very low and a geqo_effort to
a low value.
So I would definitely not agree that
Kevin Grittner kevin.gritt...@wicourts.gov writes:
I guess the question is whether there is anyone who has had a contrary
experience. (There must have been some benchmarks to justify adding
geqo at some point?)
The CVS history shows that geqo was integrated on 1997-02-19, which
I think means
Robert Haas robertmh...@gmail.com writes:
One possibility would be to remove join_collapse_limit entirely, but
that would eliminate one possibily-useful piece of functionality that
it current enables: namely, the ability to exactly specify the join
order by setting join_collapse_limit to 1.
On Tue, Jul 7, 2009 at 5:58 PM, Tom Lanet...@sss.pgh.pa.us wrote:
So while I don't doubt that geqo was absolutely essential when it was
written, it's fair to question whether it still provides a real win.
And we could definitely stand to take another look at the default
thresholds
The whole
Greg Stark gsst...@mit.edu writes:
We should benchmark the planner on increasingly large sets of
relations on a typical developer machine and set geqo to whatever
value the planner can handle in that length of time. I suspect even at
10s you're talking about substantially larger values than
On Tuesday 07 July 2009 19:45:44 Tom Lane wrote:
Greg Stark gsst...@mit.edu writes:
We should benchmark the planner on increasingly large sets of
relations on a typical developer machine and set geqo to whatever
value the planner can handle in that length of time. I suspect even at
10s
On Jul 7, 2009, at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
One possibility would be to remove join_collapse_limit entirely, but
that would eliminate one possibily-useful piece of functionality that
it current enables: namely, the ability to
Le 7 juil. 09 à 19:37, Greg Stark a écrit :
I propose that there's a maximum reasonable planning time
It sounds so much like the planner_effort GUC that has been talked
about in the past...
http://archives.postgresql.org/pgsql-performance/2009-05/msg00137.php
...except this time you want
Le 7 juil. 09 à 21:16, Robert Haas a écrit :
Now, here's another thought: if we think it's reasonable for people
to want to explicitly specify the join order, a GUC isn't really the
best fit, because it's all or nothing. Maybe we'd be better off
dropping the GUCs entirely and adding some
Dimitri Fontaine dfonta...@hi-media.com writes:
Another idea would be to have more complex metrics for deciding when
to run geqo, that is guesstimate the query planning difficulty very
quickly, based on more than just the number of relations in the from:
presence of subqueries, UNION,
Robert Haas robertmh...@gmail.com wrote:
if we think it's reasonable for people to want to explicitly specify
the join order
Regardless of the syntax (GUC or otherwise), that is an optimizer
hint. I thought we were trying to avoid those.
Although -- we do have all those enable_* GUC
Le 7 juil. 09 à 21:45, Tom Lane a écrit :
Dimitri Fontaine dfonta...@hi-media.com writes:
Another idea would be to have more complex metrics for deciding when
to run geqo
Pointless, since GEQO is only concerned with examining alternative
join
orderings. I see no reason whatever to think
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Although -- we do have all those enable_* GUC values which are also
optimizer hints; perhaps this should be another of those?
enable_join_reorder?
Not a bad suggestion, especially since turning it off would usually be
considered just about as
On Jul 7, 2009, at 3:03 PM, Kevin Grittner kevin.gritt...@wicourts.gov
wrote:
Robert Haas robertmh...@gmail.com wrote:
if we think it's reasonable for people to want to explicitly specify
the join order
Regardless of the syntax (GUC or otherwise), that is an optimizer
hint. I thought we
Robert Haas robertmh...@gmail.com writes:
I guess my point is that there's not a lot of obvious benefit in
allowing the functionality to exist but handicapping it so that it's
useful in as few cases as possible. If the consensus is that we want
half a feature (but not more or less than
Robert Haas robertmh...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
Robert Haas robertmh...@gmail.com wrote:
if we think it's reasonable for people to want to explicitly
specify the join order
Regardless of the syntax (GUC or otherwise), that is an optimizer
hint. I
Tom Lane escribió:
My own thought is that from_collapse_limit has more justification,
since it basically acts to stop a subquery from being flattened when
that would make the parent query too complex, and that seems like a
more understandable and justifiable behavior than treating JOIN
Alvaro Herrera alvhe...@commandprompt.com writes:
Tom Lane escribió:
My own thought is that from_collapse_limit has more justification,
since it basically acts to stop a subquery from being flattened when
that would make the parent query too complex, and that seems like a
more understandable
On Jul 7, 2009, at 4:56 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
I guess my point is that there's not a lot of obvious benefit in
allowing the functionality to exist but handicapping it so that it's
useful in as few cases as possible. If the consensus
Robert Haas robertmh...@gmail.com writes:
On Jul 7, 2009, at 4:56 PM, Tom Lane t...@sss.pgh.pa.us wrote:
My own thought is that from_collapse_limit has more justification,
That's pretty much where I am with it, too. The feature I was
referring to was not the collapse limits, but the
On Tue, Jul 7, 2009 at 6:33 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Jul 7, 2009, at 4:56 PM, Tom Lane t...@sss.pgh.pa.us wrote:
My own thought is that from_collapse_limit has more justification,
That's pretty much where I am with it, too. The
72 matches
Mail list logo
