pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Why we don't want hints Was: Slow count(*) again...
26+ messages / 14 participants
[nested] [flat]
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 15:50 Robert Haas <robertmhaas@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Robert Haas @ 2011-02-10 15:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Thu, Feb 3, 2011 at 8:46 PM, Josh Berkus <josh@agliodbs.com> wrote:
> "Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment instead, but a specification remains
> to be developed."
>
> That seems pretty straightforwards. There are even links to prior
> discussions about what kind of system would work. I don't think this
> text needs any adjustment; that's our clear consensus on the hint issue:
> we want a tool which works better than what we've seen in other databases.
I think it's just dumb to say we don't want hints. We want hints, or
at least many of us do. We just want them to actually work, and to
not suck. Can't we just stop saying we don't want them, and say that
we do want something, but it has to be really good?
> Yes, I occasionally run across cases where having a query tweaking
> system would help me fix a pathological failure in the planner.
> However, even on data warehouses that's less than 0.1% of the queries I
> deal with, so this isn't exactly a common event. And any hinting system
> we develop needs to address those specific cases, NOT a hypothetical
> case which can't be tested. Otherwise we'll implement hints which
> actually don't improve queries.
No argument.
The bottom line here is that a lot of features that we don't have are
things that we don't want in the sense that we're not interested in
working on them over other things that seem more pressing, and we have
finite manpower. But if someone feels motivated to work on it, and
can actually come up with something good, then why should we give the
impression that such a thing would be rejected out of hand? I think
we ought to nuke that item and replace it with some items in the
optimizer section that express what we DO want, which is some better
ways of fixing queries the few queries that suck despite our best (and
very successful) efforts to produce a top-notch optimizer.
The problem with multi-column statistics is a particularly good
example of something in this class. We may have a great solution to
that problem for PostgreSQL 11.0. But between now and then, if you
have that problem, there is no good way to adjust the selectivity
estimates. If this were an academic research project or just being
used for toy projects that didn't really matter, we might not care.
But this is a real database that people are relying on for their
livelihood, and we should be willing to provide a way for those people
to not get fired when they hit the 0.1% of queries that can't be fixed
using existing methods. I don't know exactly what the right solution
is off the top of my head, but digging in our heels is not it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 16:45 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 3 replies; 26+ messages in thread
From: Kevin Grittner @ 2011-02-10 16:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
Robert Haas <robertmhaas@gmail.com> wrote:
> I think it's just dumb to say we don't want hints. We want hints,
> or at least many of us do.
Well, yeah. Even those most ostensibly opposed to hints have been
known to post that they would rather not have the optimizer
recognize two logically equivalent constructs and optimize them the
same because they find the current difference "useful to coerce the
optimizer" to choose a certain plan. That's implementing hints but
refusing to document them. And it sometimes bites those who don't
know they're accidentally using a hint construct. An explicit and
documented hint construct would be better. Probably not a "use this
plan" type hint, but some form of optimization barrier hint, maybe.
You know, like OFFSET 0, but more explicitly hint-like.
> The bottom line here is that a lot of features that we don't have
> are things that we don't want in the sense that we're not
> interested in working on them over other things that seem more
> pressing, and we have finite manpower. But if someone feels
> motivated to work on it, and can actually come up with something
> good, then why should we give the impression that such a thing
> would be rejected out of hand? I think we ought to nuke that item
> and replace it with some items in the optimizer section that
> express what we DO want, which is some better ways of fixing
> queries the few queries that suck despite our best (and very
> successful) efforts to produce a top-notch optimizer.
>
> The problem with multi-column statistics is a particularly good
> example of something in this class. We may have a great solution
> to that problem for PostgreSQL 11.0. But between now and then, if
> you have that problem, there is no good way to adjust the
> selectivity estimates.
Yeah, this is probably the most important area to devise some
explicit way for a DBA who knows that such multicolumn selections
are going to be used, and is capable of calculating some correlation
factor, could supply it to the optimizer to override the naive
calculation it currently does. Even there I would tend to think
that the sort of "do it this way" hints that people seem to
initially want wouldn't be good; it should be a way to override the
costing factor which the optimizer gets wrong, so it can do its
usual excellent job of evaluating plans with accurate costs.
> I don't know exactly what the right solution is off the top of my
> head, but digging in our heels is not it.
Well, I'm comfortable digging in my heels against doing *lame* hints
just because "it's what all the other kids are doing," which I think
is the only thing which would have satisfied the OP on this thread.
From both on-list posts and ones exchanged off-list with me, it
seems he was stubbornly resistant to properly tuning the server to
see if any problems remained, or posting particular problems to see
how they would be most effectively handled in PostgreSQL. We
obviously can't be drawn into dumb approaches because of
ill-informed demands like that.
-Kevin
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 2 replies; 26+ messages in thread
From: Tom Lane @ 2011-02-10 17:01 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Robert Haas <robertmhaas@gmail.com> wrote:
>> I don't know exactly what the right solution is off the top of my
>> head, but digging in our heels is not it.
> Well, I'm comfortable digging in my heels against doing *lame* hints
> just because "it's what all the other kids are doing," which I think
> is the only thing which would have satisfied the OP on this thread.
Right. If someone comes up with a design that avoids the serious
pitfalls of traditional hinting schemes, that'd be great. But I'm
not interested in implementing Oracle-like hints just because Oracle
has them, which I think was basically what the OP wanted. I haven't
seen a hinting scheme that didn't suck (and that includes the aspects
of our own current behavior that are hint-like). I don't say that
there can't be one.
I believe that the FAQ entry is meant to answer people who come along
and say "oh, this is easily solved, just do what $PRODUCT does". The
generic answer to that is "no, it's not that easy". But maybe the FAQ
should be rephrased to be more like "we don't want traditional hints
because of problems X, Y, and Z. If you have an idea that avoids those
problems, let us know."
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:02 Robert Haas <robertmhaas@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 0 replies; 26+ messages in thread
From: Robert Haas @ 2011-02-10 17:02 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Well, I'm comfortable digging in my heels against doing *lame* hints
> just because "it's what all the other kids are doing," which I think
> is the only thing which would have satisfied the OP on this thread.
> From both on-list posts and ones exchanged off-list with me, it
> seems he was stubbornly resistant to properly tuning the server to
> see if any problems remained, or posting particular problems to see
> how they would be most effectively handled in PostgreSQL. We
> obviously can't be drawn into dumb approaches because of
> ill-informed demands like that.
Nor was I proposing any such thing. But that doesn't make "we don't
want hints" an accurate statement. Despite the impression that OP
went away with, the real situation is a lot more nuanced than that,
and the statement on the Todo list gives the wrong impression, IMHO.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:06 Shaun Thomas <sthomas@peak6.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 1 reply; 26+ messages in thread
From: Shaun Thomas @ 2011-02-10 17:06 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On 02/10/2011 10:45 AM, Kevin Grittner wrote:
> Even there I would tend to think that the sort of "do it this way"
> hints that people seem to initially want wouldn't be good; it should
> be a way to override the costing factor which the optimizer gets
> wrong, so it can do its usual excellent job of evaluating plans with
> accurate costs.
You know... that's an interesting approach. We already do that with
functions by allowing users to specify the estimated cost, rows
returned, and even override config settings. It's an inexact science at
best, but it might help the optimizer out.
Really... how difficult would it be to add that syntax to the JOIN
statement, for example?
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:09 Shaun Thomas <sthomas@peak6.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Shaun Thomas @ 2011-02-10 17:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On 02/10/2011 11:01 AM, Tom Lane wrote:
> But I'm not interested in implementing Oracle-like hints just because
> Oracle has them, which I think was basically what the OP wanted.
Hilariously, I'm not so sure that's what the OP wanted. Several of us
pointed him to EnterpriseDB and their Oracle-style syntax, and the only
thing he said about that was to use it as further evidence that
PostgreSQL should implement them. I'm very tempted to say he wanted
something for free, and was angry he couldn't get it.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:19 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Robert Haas @ 2011-02-10 17:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Josh Berkus <josh@agliodbs.com>; pgsql-performance
On Thu, Feb 10, 2011 at 12:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Robert Haas <robertmhaas@gmail.com> wrote:
>>> I don't know exactly what the right solution is off the top of my
>>> head, but digging in our heels is not it.
>
>> Well, I'm comfortable digging in my heels against doing *lame* hints
>> just because "it's what all the other kids are doing," which I think
>> is the only thing which would have satisfied the OP on this thread.
>
> Right. If someone comes up with a design that avoids the serious
> pitfalls of traditional hinting schemes, that'd be great. But I'm
> not interested in implementing Oracle-like hints just because Oracle
> has them, which I think was basically what the OP wanted. I haven't
> seen a hinting scheme that didn't suck (and that includes the aspects
> of our own current behavior that are hint-like). I don't say that
> there can't be one.
>
> I believe that the FAQ entry is meant to answer people who come along
> and say "oh, this is easily solved, just do what $PRODUCT does". The
> generic answer to that is "no, it's not that easy". But maybe the FAQ
> should be rephrased to be more like "we don't want traditional hints
> because of problems X, Y, and Z. If you have an idea that avoids those
> problems, let us know."
That's closer to where I think the community is on this issue, for sure.
Frankly, I think we should also have some much better documentation
about how to fix problems in the optimizer. Before the OP went off on
a rant, he actually showed up at a webinar I did looking for advice on
how to fix queries in PG, which wasn't exactly the topic of the
webinar, so he didn't get his answer. But the only way you're going
to find out about a lot of the tricks that we rely on is to read the
mailing lists, and that's below our usual standard of documentation.
Sure, it's a bunch of ugly hacks, but they're useful when you're being
eaten by a crocodile, and the need for them isn't limited to people
who have time to spend all day reading pgsql-whatever.
I also think that we have enough knowledge between us to identify the
areas where some better hints, or hint-ish mechanisms, would actually
be useful. I feel like I have a pretty good idea where the bodies are
buried, and what some of the solutions might look like. But I'm not
sure I want to open that can of worms while we're trying to close out
this CommitFest. In fact I'm pretty sure I don't. But I would like
to change the Todo text to say something less misleading.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:21 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Shaun Thomas <sthomas@peak6.com>
0 siblings, 3 replies; 26+ messages in thread
From: Kevin Grittner @ 2011-02-10 17:21 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
Shaun Thomas <sthomas@peak6.com> wrote:
> how difficult would it be to add that syntax to the JOIN
> statement, for example?
Something like this syntax?:
JOIN WITH (correlation_factor=0.3)
Where 1.0 might mean that for each value on the left there was only
one distinct value on the right, and 0.0 would mean that they were
entirely independent? (Just as an off-the-cuff example -- I'm not
at all sure that this makes sense, let alone is the best thing to
specify. I'm trying to get at *syntax* here, not particular knobs.)
-Kevin
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:27 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Kevin Grittner @ 2011-02-10 17:27 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> wrote:
>> maybe the FAQ should be rephrased to be more like "we don't want
>> traditional hints because of problems X, Y, and Z. If you have
>> an idea that avoids those problems, let us know."
>
> That's closer to where I think the community is on this issue
That sounds pretty good to me.
-Kevin
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:30 Shaun Thomas <sthomas@peak6.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 1 reply; 26+ messages in thread
From: Shaun Thomas @ 2011-02-10 17:30 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On 02/10/2011 11:21 AM, Kevin Grittner wrote:
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
I was thinking more:
JOIN foo_tab USING (foo_id) WITH (COST=50)
or something, to exploit the hooks that already exist for functions, for
example. But it's still an interesting concept. Tell the optimizer what
you want and how the data is really related in cases where it's wrong,
and let it figure out the best path.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:44 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Shaun Thomas <sthomas@peak6.com>
0 siblings, 0 replies; 26+ messages in thread
From: Kevin Grittner @ 2011-02-10 17:44 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
Shaun Thomas <sthomas@peak6.com> wrote:
> I was thinking more:
>
> JOIN foo_tab USING (foo_id) WITH (COST=50)
The problem I have with that syntax is that it would be hard to read
when you have some nested set of joins or a (SELECT) in the JOIN
instead of simple table name. For me, at least, it would "get lost"
less easily if it were right next to the JOIN keyword.
The problem with a COST factor is that it's not obvious to me what
it would apply to:
- each row on the left?
- each row on the right?
- each row in the result of the JOIN step?
- the entire step?
How would it scale based on other criteria which affected the number
of rows on either side of the join?
If I'm understanding the problem correctly, the part the optimizer
gets wrong (because we don't yet have statistics to support a better
assumption) is assuming that selection criteria on opposite sides of
a join affect entirely independent sets of what would be in the
result without the criteria. To use an oft-cited example, when one
table is selected by zip code and the other by city, that's a bad
assumption about the correlation, leading to bad estimates, leading
to bad costing, leading to bad plans. The OP wanted to override
step 4, a COST setting would try to override step 3, but I think we
would want to override step 1 (until we get statistics which let us
compute that accurately).
-Kevin
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 17:56 Greg Smith <greg@2ndquadrant.com>
parent: Shaun Thomas <sthomas@peak6.com>
0 siblings, 1 reply; 26+ messages in thread
From: Greg Smith @ 2011-02-10 17:56 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
Shaun Thomas wrote:
> Hilariously, I'm not so sure that's what the OP wanted.
Someone to blame as a scapegoat for why his badly planned project had
failed. I've done several Oracle conversions before, and never met
someone who was so resistent to doing the right things for such a
conversion. You have to relatively flexible in your thinking to work
with the good and away from the bad parts of PostgreSQL for such a
project to succeed. I didn't hear a whole lot of "flexible" in that
discussion.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 18:26 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 26+ messages in thread
From: Kevin Grittner @ 2011-02-10 18:26 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; sthomas@peak6.com; +Cc: Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance; Tom Lane <tgl@sss.pgh.pa.us>
Greg Smith <greg@2ndquadrant.com> wrote:
> Shaun Thomas wrote:
>> Hilariously, I'm not so sure that's what the OP wanted.
>
> Someone to blame as a scapegoat for why his badly planned project
> had failed. I've done several Oracle conversions before, and
> never met someone who was so resistent to doing the right things
> for such a conversion. You have to relatively flexible in your
> thinking to work with the good and away from the bad parts of
> PostgreSQL for such a project to succeed. I didn't hear a whole
> lot of "flexible" in that discussion.
I was thinking along the same lines, but couldn't find the words to
put it so politely, so I held back. Still biting my tongue, but I
appreciate your milder summary.
-Kevin
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 18:32 Craig James <craig_james@emolecules.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 1 reply; 26+ messages in thread
From: Craig James @ 2011-02-10 18:32 UTC (permalink / raw)
To: pgsql-performance
On 2/10/11 9:21 AM, Kevin Grittner wrote:
> Shaun Thomas<sthomas@peak6.com> wrote:
>
>> how difficult would it be to add that syntax to the JOIN
>> statement, for example?
>
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
>
> Where 1.0 might mean that for each value on the left there was only
> one distinct value on the right, and 0.0 would mean that they were
> entirely independent? (Just as an off-the-cuff example -- I'm not
> at all sure that this makes sense, let alone is the best thing to
> specify. I'm trying to get at *syntax* here, not particular knobs.)
There are two types of problems:
1. The optimizer is imperfect and makes a sub-optimal choice.
2. There is theoretical reasons why it's hard for the optimizer. For example, in a table with 50 columns, there is a staggering number of possible correlations. An optimizer can't possibly figure this out, but a human might know them from the start. The City/Postal-code correlation is a good example.
For #1, Postgres should never offer any sort of hint mechanism. As many have pointed out, it's far better to spend the time fixing the optimizer than adding hacks.
For #2, it might make sense to give a designer a way to tell Postgres stuff that it couldn't possibly figure out. But ... not until the problem is clearly defined.
What should happen is that someone writes with an example query, and the community realizes that no amount of cleverness from Postgres could ever solve it (for solid theoretical reasons). Only then, when the problem is clearly defined, should we talk about solutions and SQL extensions.
Craig
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 20:55 Tobias Brox <tobixen@gmail.com>
1 sibling, 3 replies; 26+ messages in thread
From: Tobias Brox @ 2011-02-10 20:55 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, Mladen Gogala <mladen.gogala@vmsinfo.com>
On 4 February 2011 04:46, Josh Berkus <josh@agliodbs.com> wrote:
> "Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment instead, but a specification remains
> to be developed."
I have no clue about how hints works in Oracle ... I've never been
working "enterprise level" on anything else than Postgres. Anyway,
today I just came over an interesting problem in our production
database today - and I think it would be a benefit to be able to
explicitly tell the planner what index to use (the dev team is adding
redundant attributes and more indexes to solve the problem - which
worries me, because we will run into serious problems as soon as there
won't be enough memory for all the frequently-used indexes).
We have users and transactions, and we have transaction types. The
transaction table is huge. The users are able to interactively check
their transaction listings online, and they have some simple filter
options available as well. Slightly simplified, the queries done
looks like this:
select * from account_transaction where account_id=? order by
created desc limit 25;
select * from account_transaction where trans_type_id in ( ...
long, hard-coded list ...) and account_id=? order by created desc
limit 25;
and we have indexes on:
account_transaction(account_id, created)
account_transaction(account_id, trans_type_id, created)
(At this point, someone would probably suggest to make three
single-key indexes and use bitmap index scan ... well, pulling 25 rows
from the end of an index may be orders of magnitude faster than doing
bitmap index mapping on huge indexes)
For the second query, the planner would chose the first index - and
maybe it makes sense - most of our customers have between 10-30% of
the transactions from the long list of transaction types, slim indexes
are good and by average the slimmer index would probably do the job a
bit faster. The problem is with the corner cases - for some of our
extreme customers thousands of transaction index tuples may need to be
scanned before 25 rows with the correct transaction type is pulled
out, and if the index happens to be on disk, it may take tens of
seconds to pull out the answer. Tens of seconds of waiting leads to
frustration, it is a lot nowadays in an interactive session. Also, I
haven't really checked it up, but it may very well be that this is
exactly the kind of customers we want to retain.
To summarize, there are two things the planner doesn't know - it
doesn't know that there exists such corner cases where the real cost
is far larger than the estimated cost, and it doesn't know that it's
more important to keep the worst-case cost on a reasonable level than
to minimize the average cost. In the ideal world postgres would have
sufficiently good statistics to know that for user #77777 it is better
to chose the second index, but I suppose it would be easier if I was
able to explicitly hide the account_transaction(account_id, created)
index for this query. Well, I know of one way to do it ... but I
suppose it's not a good idea to put "drop index foo; select ...;
rollback;" into production ;-)
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-10 21:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tobias Brox <tobixen@gmail.com>
2 siblings, 0 replies; 26+ messages in thread
From: Tom Lane @ 2011-02-10 21:12 UTC (permalink / raw)
To: Tobias Brox <tobixen@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Tobias Brox <tobixen@gmail.com> writes:
> I have no clue about how hints works in Oracle ... I've never been
> working "enterprise level" on anything else than Postgres. Anyway,
> today I just came over an interesting problem in our production
> database today - and I think it would be a benefit to be able to
> explicitly tell the planner what index to use (the dev team is adding
> redundant attributes and more indexes to solve the problem - which
> worries me, because we will run into serious problems as soon as there
> won't be enough memory for all the frequently-used indexes).
> We have users and transactions, and we have transaction types. The
> transaction table is huge. The users are able to interactively check
> their transaction listings online, and they have some simple filter
> options available as well. Slightly simplified, the queries done
> looks like this:
> select * from account_transaction where account_id=? order by
> created desc limit 25;
> select * from account_transaction where trans_type_id in ( ...
> long, hard-coded list ...) and account_id=? order by created desc
> limit 25;
> and we have indexes on:
> account_transaction(account_id, created)
> account_transaction(account_id, trans_type_id, created)
Well, in this case the optimizer *is* smarter than you are, and the
reason is that it remembers the correct rules for when indexes are
useful. That second index is of no value for either query, because
"in" doesn't work the way you're hoping.
I understand the larger point you're trying to make, but this example
also nicely illustrates the point being made on the other side, that
"force the optimizer to use the index I think it should use" isn't a
very good solution.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-11 08:19 Віталій Тимчишин <tivv00@gmail.com>
parent: Tobias Brox <tobixen@gmail.com>
2 siblings, 1 reply; 26+ messages in thread
From: Віталій Тимчишин @ 2011-02-11 08:19 UTC (permalink / raw)
To: Tobias Brox <tobixen@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Mladen Gogala <mladen.gogala@vmsinfo.com>
2011/2/10 Tobias Brox <tobixen@gmail.com>
> On 4 February 2011 04:46, Josh Berkus <josh@agliodbs.com> wrote:
> > "Optimizer hints are used to work around problems in the optimizer and
> > introduce upgrade and maintenance issues. We would rather have the
> > problems reported and fixed. We have discussed a more sophisticated
> > system of per-class cost adjustment instead, but a specification remains
> > to be developed."
>
> I have no clue about how hints works in Oracle ... I've never been
> working "enterprise level" on anything else than Postgres. Anyway,
> today I just came over an interesting problem in our production
> database today - and I think it would be a benefit to be able to
> explicitly tell the planner what index to use (the dev team is adding
> redundant attributes and more indexes to solve the problem - which
> worries me, because we will run into serious problems as soon as there
> won't be enough memory for all the frequently-used indexes).
>
> We have users and transactions, and we have transaction types. The
> transaction table is huge. The users are able to interactively check
> their transaction listings online, and they have some simple filter
> options available as well. Slightly simplified, the queries done
> looks like this:
>
> select * from account_transaction where account_id=? order by
> created desc limit 25;
>
> select * from account_transaction where trans_type_id in ( ...
> long, hard-coded list ...) and account_id=? order by created desc
> limit 25;
>
> and we have indexes on:
>
> account_transaction(account_id, created)
>
> account_transaction(account_id, trans_type_id, created)
>
> If the list is hard-coded, you can create partial index on
account_transaction(account_id, created desc) where trans_type_id in ( ...
long, hard-coded list ...)
--
Best regards,
Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-11 09:29 Tobias Brox <tobixen@gmail.com>
parent: Віталій Тимчишин <tivv00@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Tobias Brox @ 2011-02-11 09:29 UTC (permalink / raw)
To: Віталій Тимчишин <tivv00@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Mladen Gogala <mladen.gogala@vmsinfo.com>
2011/2/11 Віталій Тимчишин <tivv00@gmail.com>:
> If the list is hard-coded, you can create partial index on
> account_transaction(account_id, created desc) where trans_type_id in ( ...
> long, hard-coded list ...)
My idea as well, though it looks ugly and it would be a maintenance
head-ache (upgrading the index as new transaction types are added
would mean "costly" write locks on the table, and we can't rely on
manual processes to get it right ... we might need to set up scripts
to either upgrade the index or alert us if the index needs upgrading).
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-11 09:44 Vitalii Tymchyshyn <tivv00@gmail.com>
parent: Tobias Brox <tobixen@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Vitalii Tymchyshyn @ 2011-02-11 09:44 UTC (permalink / raw)
To: Tobias Brox <tobixen@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Mladen Gogala <mladen.gogala@vmsinfo.com>
11.02.11 11:29, Tobias Brox написав(ла):
> 2011/2/11 Віталій Тимчишин<tivv00@gmail.com>:
>> If the list is hard-coded, you can create partial index on
>> account_transaction(account_id, created desc) where trans_type_id in ( ...
>> long, hard-coded list ...)
> My idea as well, though it looks ugly and it would be a maintenance
> head-ache (upgrading the index as new transaction types are added
> would mean "costly" write locks on the table,
Create new one concurrently.
> and we can't rely on
> manual processes to get it right ... we might need to set up scripts
> to either upgrade the index or alert us if the index needs upgrading).
Yep. Another option could be to add query rewrite as
select * from (
select * from account_transaction where trans_type_id =type1 and
account_id=? order by created desc limit 25 union all
select * from account_transaction where trans_type_id =type2 and
account_id=? order by created desc limit 25 union all
...
union all
select * from account_transaction where trans_type_id =typeN and
account_id=? order by created desc limit 25
) a
order by created desc limit 25
This will allow to use three-column index in the way it can be used for
such query. Yet if N is large query will look ugly. And I am not sure if
optimizer is smart enough for not to fetch 25*N rows.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-11 11:26 Tobias Brox <tobixen@gmail.com>
parent: Vitalii Tymchyshyn <tivv00@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Tobias Brox @ 2011-02-11 11:26 UTC (permalink / raw)
To: Vitalii Tymchyshyn <tivv00@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Mladen Gogala <mladen.gogala@vmsinfo.com>
2011/2/11 Vitalii Tymchyshyn <tivv00@gmail.com>:
>> My idea as well, though it looks ugly and it would be a maintenance
>> head-ache (upgrading the index as new transaction types are added
>> would mean "costly" write locks on the table,
>
> Create new one concurrently.
Concurrently? Are there any ways to add large indexes without
blocking inserts to the table for the time it takes to create the
index?
> Yep. Another option could be to add query rewrite as
>
> select * from (
> select * from account_transaction where trans_type_id =type1 and
> account_id=? order by created desc limit 25 union all
> select * from account_transaction where trans_type_id =type2 and
> account_id=? order by created desc limit 25 union all
> ...
> union all
> select * from account_transaction where trans_type_id =typeN and
> account_id=? order by created desc limit 25
> ) a
> order by created desc limit 25
I actually considered that. For the test case given it works very
fast. Not sure if it would work universally ... it scales well when
having extreme amounts of transactions outside the given transaction
list (the case we have problems with now), but it wouldn't scale if
some user has an extreme amount of transactions within the list.
However, I think our "extreme amount of transactions"-problem is
mostly limited to the transaction types outside the list.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-11 11:33 Andrea Suisani <sickpig@opinioni.net>
parent: Vitalii Tymchyshyn <tivv00@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Andrea Suisani @ 2011-02-11 11:33 UTC (permalink / raw)
To: Tobias Brox <tobixen@gmail.com>; +Cc: Vitalii Tymchyshyn <tivv00@gmail.com>; Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Mladen Gogala <mladen.gogala@vmsinfo.com>
On 02/11/2011 12:26 PM, Tobias Brox wrote:
> 2011/2/11 Vitalii Tymchyshyn<tivv00@gmail.com>:
>>> My idea as well, though it looks ugly and it would be a maintenance
>>> head-ache (upgrading the index as new transaction types are added
>>> would mean "costly" write locks on the table,
>>
>> Create new one concurrently.
>
> Concurrently? Are there any ways to add large indexes without
> blocking inserts to the table for the time it takes to create the
> index?
yep, AFAIR since 8.2
see: http://www.postgresql.org/docs/8.2/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
[cut]
Andrea
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-11 14:51 Pierre C <lists@peufeu.com>
parent: Tobias Brox <tobixen@gmail.com>
2 siblings, 0 replies; 26+ messages in thread
From: Pierre C @ 2011-02-11 14:51 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Tobias Brox <tobixen@gmail.com>; +Cc: pgsql-performance@postgresql.org, "Mladen Gogala" <mladen.gogala@vmsinfo.com>
> select * from account_transaction where trans_type_id in ( ...
> long, hard-coded list ...) and account_id=? order by created desc
> limit 25;
You could use an index on (account_id, created, trans_type), in
replacement of your index on (account_id, created). This will not prevent
the "Index Scan Backwards", but at least, index rows with trans_type not
matching the WHERE clause will not generate any heap access...
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-16 21:22 Bruce Momjian <bruce@momjian.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 1 reply; 26+ messages in thread
From: Bruce Momjian @ 2011-02-16 21:22 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: sthomas@peak6.com, Josh Berkus <josh@agliodbs.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
Kevin Grittner wrote:
> Shaun Thomas <sthomas@peak6.com> wrote:
>
> > how difficult would it be to add that syntax to the JOIN
> > statement, for example?
>
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
>
> Where 1.0 might mean that for each value on the left there was only
> one distinct value on the right, and 0.0 would mean that they were
> entirely independent? (Just as an off-the-cuff example -- I'm not
> at all sure that this makes sense, let alone is the best thing to
> specify. I'm trying to get at *syntax* here, not particular knobs.)
I am not excited about the idea of putting these correlations in
queries. What would be more intesting would be for analyze to build a
correlation coeffficent matrix showing how columns are correlated:
a b c
a 1 .4 0
b .1 1 -.3
c .2 .3 1
and those correlations could be used to weigh how the single-column
statistics should be combined.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-02-23 02:22 Robert Haas <robertmhaas@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 26+ messages in thread
From: Robert Haas @ 2011-02-23 02:22 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; sthomas@peak6.com, Josh Berkus <josh@agliodbs.com>; pgsql-performance
On Wed, Feb 16, 2011 at 4:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
> I am not excited about the idea of putting these correlations in
> queries. What would be more intesting would be for analyze to build a
> correlation coeffficent matrix showing how columns are correlated:
>
> a b c
> a 1 .4 0
> b .1 1 -.3
> c .2 .3 1
>
> and those correlations could be used to weigh how the single-column
> statistics should be combined.
If you can make it work, I'll take it... it's (much) easier said than
done, though.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-06-05 15:25 pasman pasmański <pasman.p@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: pasman pasmański @ 2011-06-05 15:25 UTC (permalink / raw)
To: pgsql-performance
Hi. I have the idea: hints joined to function. For example instead of
WHERE table1.field1=table2.field2
write:
WHERE specificeq(table1.field1,table2.field2)
and hints add to declaration of specificeq function.
2011/2/23, Robert Haas <robertmhaas@gmail.com>:
> On Wed, Feb 16, 2011 at 4:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> I am not excited about the idea of putting these correlations in
>> queries. What would be more intesting would be for analyze to build a
>> correlation coeffficent matrix showing how columns are correlated:
>>
>> a b c
>> a 1 .4 0
>> b .1 1 -.3
>> c .2 .3 1
>>
>> and those correlations could be used to weigh how the single-column
>> statistics should be combined.
>
> If you can make it work, I'll take it... it's (much) easier said than
> done, though.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
------------
pasman
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Why we don't want hints Was: Slow count(*) again...
@ 2011-06-06 08:14 Robert Klemme <shortcutter@googlemail.com>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 26+ messages in thread
From: Robert Klemme @ 2011-06-06 08:14 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Thu, Feb 10, 2011 at 7:32 PM, Craig James <craig_james@emolecules.com> wrote:
> On 2/10/11 9:21 AM, Kevin Grittner wrote:
>>
>> Shaun Thomas<sthomas@peak6.com> wrote:
>>
>>> how difficult would it be to add that syntax to the JOIN
>>> statement, for example?
>>
>> Something like this syntax?:
>>
>> JOIN WITH (correlation_factor=0.3)
>>
>> Where 1.0 might mean that for each value on the left there was only
>> one distinct value on the right, and 0.0 would mean that they were
>> entirely independent? (Just as an off-the-cuff example -- I'm not
>> at all sure that this makes sense, let alone is the best thing to
>> specify. I'm trying to get at *syntax* here, not particular knobs.)
>
> There are two types of problems:
>
> 1. The optimizer is imperfect and makes a sub-optimal choice.
>
> 2. There is theoretical reasons why it's hard for the optimizer. For
> example, in a table with 50 columns, there is a staggering number of
> possible correlations. An optimizer can't possibly figure this out, but a
> human might know them from the start. The City/Postal-code correlation is a
> good example.
>
> For #1, Postgres should never offer any sort of hint mechanism. As many
> have pointed out, it's far better to spend the time fixing the optimizer
> than adding hacks.
>
> For #2, it might make sense to give a designer a way to tell Postgres stuff
> that it couldn't possibly figure out. But ... not until the problem is
> clearly defined.
>
> What should happen is that someone writes with an example query, and the
> community realizes that no amount of cleverness from Postgres could ever
> solve it (for solid theoretical reasons). Only then, when the problem is
> clearly defined, should we talk about solutions and SQL extensions.
I don't have one such query handy. However, I think your posting is a
good starting point for a discussion how to figure out what we need
and how a good solution could look like. For example, one thing I
dislike about hints is that they go into the query. There are a few
drawbacks of this approach
- Applications need to be changed to benefit which is not always possible.
- One important class of such applications are those that use OR
mappers - hinting then would have to be buried in OR mapper code or
configuration.
- Hints in the query work only for exactly that query (this might be
an advantage depending on point of view).
I think the solution should rather be to tell Postgres what "it
couldn't possibly figure out". I imagine that could be some form of
description of the distribution of data in columns and / or
correlations between columns. Advantage would be that the optimizer
gets additional input which it can use (i.e. the usage can change
between releases), the information is separate from queries (more like
meta data for tables) and thus all queries using a particular table
which was augmented with this meta data would benefit. Usage of this
meta data could be controlled by a flag per session (as well as
globally) so it would be relatively easy to find out whether this meta
data has become obsolete (because data changed or a new release of the
database is in use).
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2011-06-06 08:14 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-10 15:50 ` Robert Haas <robertmhaas@gmail.com>
2011-02-10 16:45 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-02-10 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-10 17:09 ` Shaun Thomas <sthomas@peak6.com>
2011-02-10 17:56 ` Greg Smith <greg@2ndquadrant.com>
2011-02-10 18:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-02-10 17:19 ` Robert Haas <robertmhaas@gmail.com>
2011-02-10 17:27 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-02-10 17:02 ` Robert Haas <robertmhaas@gmail.com>
2011-02-10 17:06 ` Re: Why we don't want hints Was: Slow count(*) again... Shaun Thomas <sthomas@peak6.com>
2011-02-10 17:21 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-02-10 17:30 ` Re: Why we don't want hints Was: Slow count(*) again... Shaun Thomas <sthomas@peak6.com>
2011-02-10 17:44 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-02-10 18:32 ` Re: Why we don't want hints Was: Slow count(*) again... Craig James <craig_james@emolecules.com>
2011-06-06 08:14 ` Robert Klemme <shortcutter@googlemail.com>
2011-02-16 21:22 ` Bruce Momjian <bruce@momjian.us>
2011-02-23 02:22 ` Robert Haas <robertmhaas@gmail.com>
2011-06-05 15:25 ` pasman pasmański <pasman.p@gmail.com>
2011-02-10 20:55 ` Tobias Brox <tobixen@gmail.com>
2011-02-10 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-11 08:19 ` Віталій Тимчишин <tivv00@gmail.com>
2011-02-11 09:29 ` Tobias Brox <tobixen@gmail.com>
2011-02-11 09:44 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2011-02-11 11:26 ` Tobias Brox <tobixen@gmail.com>
2011-02-11 11:33 ` Andrea Suisani <sickpig@opinioni.net>
2011-02-11 14:51 ` Pierre C <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox