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
>
> > 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
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
c
"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
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
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 w
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 y
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 su
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
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
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.
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
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 consider
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. Howev
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 con
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 enable_seq
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
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...
> >
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", ev
On 12/15/05, David Lang <[EMAIL PROTECTED]> wrote:
> On Thu, 15 Dec 2005, Craig A. James wrote:
>
> > 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 i
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 ot
On Thu, 15 Dec 2005, Craig A. James wrote:
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
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
today.
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
... 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 discus
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!"
Ye
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
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 desig
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
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 develo
Boy, you picked a *really* bad example ;-)
The problem is that Postgres decided to filter on myfunc() *first*, and
then filter on row_num, resulting in a query time that jumped from
seconds to hours. And there's no way for me to tell Postgres not to do
that!
Can you paste explain analyze an
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:
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
ro
> Right on. Some of these "coerced" plans may perform > much 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.
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 c
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
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.
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 Casan
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
"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 plan
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
41 matches
Mail list logo
