On Sat, Dec 17, 2005 at 07:31:40AM -0500, Jaime Casanova wrote:
Yeah it would - an implementation I have seen that I like is where the
developer can supply the *entire* execution plan with a query. This is
complex enough to make casual use unlikely :-), but provides the ability
to try
On Fri, 16 Dec 2005, Mark Kirkwood wrote:
Craig A. James wrote:
What would be cool would be some way the developer could alter the plan,
but they way of doing so would strongly encourage the developer to send the
information to this mailing list. Postgres would essentially say, Ok, you
Yeah it would - an implementation I have seen that I like is where the
developer can supply the *entire* execution plan with a query. This is
complex enough to make casual use unlikely :-), but provides the ability
to try out other plans, and also fix that vital query that must run
Kevin Brown wrote:
Craig A. James wrote:
Hints are dangerous, and I consider them a last resort.
If you consider them a last resort, then why do you consider them to
be a better alternative than a workaround such as turning off
enable_seqscan, when all the other tradeoffs are
On 12/16/05, Kyle Cordes [EMAIL PROTECTED] wrote:
Kevin Brown wrote:
Craig A. James wrote:
Hints are dangerous, and I consider them a last resort.
If you consider them a last resort, then why do you consider them to
be a better alternative than a workaround such as turning off
Dnia 16-12-2005, pią o godzinie 16:16 +1300, Mark Kirkwood napisał(a):
Craig A. James wrote:
What would be cool would be some way the developer could alter the plan,
but they way of doing so would strongly encourage the developer to send
the information to this mailing list.
Jaime Casanova wrote:
The context is this - in a busy OLTP system, sometimes a query comes
through that, for whatever reason (foolishness on my part as a
developer, unexpected use by a user, imperfection of the optimizer,
etc.), takes a really long time to run, usually because it table-scans
one
On Thu, 2005-12-15 at 18:23 -0800, Craig A. James wrote:
So, you still have no problem is exactly wrong, because Postgres picked the
wrong plan. Postgres decided that applying myfunc() to 10,000,000 rows was a
better plan than an index scan of 50,000 row_nums. So I'm screwed.
FWIW,
The
On Thu, Dec 15, 2005 at 09:48:55PM -0800, Kevin Brown wrote:
Craig A. James wrote:
Kevin Brown wrote:
Hints are dangerous, and I consider them a last resort.
If you consider them a last resort, then why do you consider them to
be a better alternative than a workaround such as turning
On Fri, Dec 16, 2005 at 03:31:03PM +1300, Mark Kirkwood wrote:
After years of using several other database products (some supporting
hint type constructs and some not), I have come to believe that hinting
(or similar) actually *hinders* the development of a great optimizer.
I don't think you
On Fri, Dec 16, 2005 at 04:16:58PM +1300, Mark Kirkwood wrote:
Craig A. James wrote:
What would be cool would be some way the developer could alter the plan,
but they way of doing so would strongly encourage the developer to send
the information to this mailing list. Postgres would
Jaime Casanova wrote:
What I would
really like is for my DBMS to give me a little more pushback - I'd like
to ask it to run a query, and have it either find a good way to run
the query, or politely refuse to run it at all.
set statement_timeout in postgresql.conf
That is what I am
Craig A. James [EMAIL PROTECTED] writes:
How about this: Instead of arguing in the abstract, tell me in
concrete terms how you would address the very specific example I gave,
where myfunc() is a user-written function. To make it a little more
challenging, try this: myfunc() can behave very
I asked a while back if there were any plans to allow developers to override the
optimizer's plan and force certain plans, and received a fairly resounding
No. The general feeling I get is that a lot of work has gone into the
optimizer, and by God we're going to use it!
I think this is just
Craig A. James [EMAIL PROTECTED] writes:
I see this over and over. Tweak the parameters to force a certain
plan, because there's no formal way for a developer to say, I know
the best plan.
I think you've misunderstood those conversations entirely. The point
is not to force the planner into a
select * from my_table where row_num = 5 and row_num 10
and myfunc(foo, bar);
You just create an index on myfunc(foo, bar)
Chris
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
On 12/15/05, Christopher Kings-Lynne [EMAIL PROTECTED] wrote:
select * from my_table where row_num = 5 and row_num 10
and myfunc(foo, bar);
You just create an index on myfunc(foo, bar)
Chris
only if myfunc(foo, bar) is immutable...
--
regards,
Jaime Casanova
(DBA:
select * from my_table where row_num = 5 and row_num 10
and myfunc(foo, bar);
You just create an index on myfunc(foo, bar)
only if myfunc(foo, bar) is immutable...
And if it's not then the best any database can do is to index scan
row_num - so still you have no problem.
Tom,
I see this over and over. Tweak the parameters to force a certain
plan, because there's no formal way for a developer to say, I know
the best plan.
I think you've misunderstood those conversations entirely. The point
is not to force the planner into a certain plan, it is to explore
Christopher Kings-Lynne wrote:
select * from my_table where row_num = 5 and row_num 10
and myfunc(foo, bar);
You just create an index on myfunc(foo, bar)
Thanks, but myfunc() takes parameters (shown here as foo, bar), one of which
is not a column, it's external and
Right on. Some of these coerced plans may performmuch better.
If so, we can look at tweaking your runtime
config: e.g.
effective_cache_size
random_page_cost
default_statistics_target
to see if said plans can be chosen naturally.
I see this over and over. Tweak the
Christopher Kings-Lynne wrote:
select * from my_table where row_num = 5 and row_num
10
and myfunc(foo, bar);
You just create an index on myfunc(foo, bar)
only if myfunc(foo, bar) is immutable...
And if it's not then the best any database can do is to index scan
Craig A. James wrote:
I asked a while back if there were any plans to allow developers to
override the optimizer's plan and force certain plans, and received a
fairly resounding No. The general feeling I get is that a lot of work
has gone into the optimizer, and by God we're going to use it!
Christopher Kings-Lynne wrote:
I don't necessarily disagree with your assertion that we need planner
hints, but unless you or someone else is willing to submit a patch with
the feature it's unlikely to ever be implemented...
Now that's an answer I understand and appreciate. Open-source
Mark Kirkwood wrote:
I hear what you are saying, but to use this fine example - I don't know
what the best plan is - these experiments part of an investigation to
find *if* there is a better plan, and if so, why Postgres is not finding
it.
There isn't a database in the world that is as smart
Tom Lane wrote:
This discussion has been had before (many times) ... see the -hackers
archives for detailed arguments. The one that carries the most weight
in my mind is that planner hints embedded in applications will not adapt
to changing circumstances --- the plan that was best when you
Christopher Kings-Lynne wrote:
Can you paste explain analyze and your effective_cache_size, etc. settings.
...
This seems like a case where PostgreSQL's current optimiser should
easily know what to do if your config settings are correct and you've
been running ANALYZE, so I'd like to see your
Craig A. James wrote:
What would be cool would be some way the developer could alter the plan,
but they way of doing so would strongly encourage the developer to send
the information to this mailing list. Postgres would essentially say,
Ok, you can do that, but we want to know why!
... This seems like a case where PostgreSQL's current optimiser should
easily know what to do if your config settings are correct and you've
been running ANALYZE, so I'd like to see your settings and the explain
analyze plan...
I could, but it would divert us from the main topic of this
Christopher Kings-Lynne wrote:
So your main example bad query is possibly just a case of lack of
analyze stats and wrong postgresql.conf config? And that's what causes
you to shut down your database? Don't you want your problem FIXED?
I'm trying to help by raising a question that I think is
On 12/15/05, Craig A. James [EMAIL PROTECTED] wrote:
Yeah it would - an implementation I have seen that I like is where the
developer can supply the *entire* execution plan with a query. This is
complex enough to make casual use unlikely :-), but provides the ability
to try out other
On Thu, 15 Dec 2005, Craig A. James wrote:
The example I raised in a previous thread, of irregular usage, is the same: I
have a particular query that I *always* want to be fast even if it's only
used rarely, but the system swaps its tables out of the file-system cache,
based on low usage,
Craig A. James wrote:
Christopher Kings-Lynne wrote:
select * from my_table where row_num = 5 and row_num
10
and myfunc(foo, bar);
You just create an index on myfunc(foo, bar)
only if myfunc(foo, bar) is immutable...
And if it's not then the best any
Craig A. James wrote:
Hints are dangerous, and I consider them a last resort.
If you consider them a last resort, then why do you consider them to
be a better alternative than a workaround such as turning off
enable_seqscan, when all the other tradeoffs are considered?
If your argument is that
Kevin Brown wrote:
Hints are dangerous, and I consider them a last resort.
If you consider them a last resort, then why do you consider them to
be a better alternative than a workaround such as turning off
enable_seqscan, when all the other tradeoffs are considered?
If I understand
Craig A. James wrote:
Kevin Brown wrote:
Hints are dangerous, and I consider them a last resort.
If you consider them a last resort, then why do you consider them to
be a better alternative than a workaround such as turning off
enable_seqscan, when all the other tradeoffs are considered?
On Thu, Dec 15, 2005 at 21:41:06 -0800,
Craig A. James [EMAIL PROTECTED] wrote:
If I understand enable_seqscan, it's an all-or-nothing affair. Turning it
off turns it off for the whole database, right? The same is true of all of
You can turn it off just for specific queries. However, it
37 matches
Mail list logo