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
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
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 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 se
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
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 overhea
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 thinkin
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
Physical using xfs on Linux.
- Luke
--
Sent from my BlackBerry Wireless Device
-Original Message-
From: Anjan Dave <[EMAIL PROTECTED]>
To: Luke Lonergan <[EMAIL PROTECTED]>; Charles Sprickman <[EMAIL PROTECTED]>;
pgsql-performance@postgresql.org
Sent: Thu Dec 15
Luke,
How did you measure 800MB/sec, is it cached, or physical I/O?
-anjan
-Original Message-
From: Luke Lonergan [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 14, 2005 2:10 AM
To: Charles Sprickman; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] SAN/NAS options
Charles,
Mitch Skinner wrote:
I saw that; what I'm suggesting is that that you try creating a 3-column
index on ordered_products using the paid, suspended_sub, and id columns.
In that order, I think, although you could also try the reverse. It may
or may not help, but it's worth a shot--the fact that all
Well, what does the random_page_cost do internally ?
I don't think I'd expect postgres to be able to *do* anything in particular,
any more than I would expect it to "do" something about slow disk I/O or having
limited cache. But it might be useful to the EXPLAIN ANALYZE in estimating
costs of r
Hi, Gregory,
Gregory S. Williamson wrote:
> Forgive the cross-posting, but I found myself wondering if might not
> be some way future way of telling the planner that a given table
> (column ?) has a high likelyhood of being TOASTed. Similar to the
> random_page_cost in spirit. We've got a lot of i
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.
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).
I
39 matches
Mail list logo
