Kevin Brown wrote:
On Wednesday 14 December 2005 18:36, you wrote:
Well - that had no effect at all :-) You don't have and index on
to_ship.ordered_product_id do you? - try adding one (ANALYZE again), and
let use know what happens (you may want to play with SET
enable_seqscan=off as well).
On Thu, 2005-12-15 at 01:48 -0600, Kevin Brown wrote:
Well, I'm no expert either, but if there was an index on
ordered_products (paid, suspended_sub, id) it should be mergejoinable
with the index on to_ship.ordered_product_id, right? Given the
conditions on paid and suspended_sub.
The
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
On Mon, Dec 12, 2005 at 10:20:45PM -0500, Tom Lane wrote:
Given the rather lackadaisical way in which the stats collector makes
the data available, it seems like the backends are being much too
enthusiastic about posting their stats_command_string status
immediately. Might be worth thinking
Michael Fuhr [EMAIL PROTECTED] writes:
Does the backend support, or could it be easily modified to support,
a mechanism that would post the command string after a configurable
amount of time had expired, and then continue processing the query?
Not really, unless you want to add the overhead of
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 Fri, 16 Dec 2005, Mark Kirkwood wrote:
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.
Mark, I've seen
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
Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
Does the backend support, or could it be easily modified to support,
a mechanism that would post the command string after a configurable
amount of time had expired, and then continue processing the query?
Not really, unless you want
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
30 matches
Mail list logo
