Big disappointment with Postgres
Mladen Gogala
similar to Oracle. Unfortunately, when it came down to the pilot project,
the mass of the SQL to be ported ultimately required optimizer hints.
There was simply a mass of SQL, ported to different architecture which
needed quick & dirty fix, because of the sheer volume and the deadline.
Unfortunately, Postgres community is run by programming wiz kids who have
never managed a large database and they sanctimoniously refuse to even
consider hints.
Consequently, I have to kill the pilot project. Postgres is, simply, not
a viable alternative. Not with that attitude, anyway.
John Hurley
# Unfortunately, Postgres community is run by programming wiz kids who
have never managed a large database and they sanctimoniously refuse to
even consider hints.
Aren't hints in oracle code an extreme example of "extensions" to the
SQL language?
Do any other relational databases support hints?
Obviously if Oracle supports the database code they are free to
introduce proprietary extensions as they see fit into their code base.
Does MySQL support hinting the way Oracle does?
gazzag
Good plug for Oracle support :D
Seriously though, is this a fair comparison?
-g
Mark D Powell
I know that SQL Server has some optimizer hints available so Oracle is
definitely not the only vendor to provide developers a means to adjust
the optimizer plan.
According to the official mySQL manual it also supports hints:
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
HTH -- Mark D Powell --
Mark D Powell
Update - It also appears that postgreSQL has a hint feature:
http://www.enterprisedb.com/docs/en/8.3R2/perf/Postgres_Plus_Advanced_Server_Performance_Guide-28.htm
Mladen Gogala
DB2 too:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/
com.ibm.db2.doc.admin/p9li375.htm
Oracle, DB2, SQL Server and MySQL all provide hints. Hints are nothing
unusual, they're the necessary fix with the large projects. The project
in question was a porting project with a deadline. There was a ton of SQL
that needed to be ported fast. Without hints, this is not possible,
therefore, the Postgres pilot project was canceled. With hints, there
would probably be far fewer consulting gigs for the postgres gurus, which
may help explaining the religious zeal. Oracle is expensive, but not as
expensive as the downtime. Bummer.
Mladen Gogala
> Update - It also appears that postgreSQL has a hint feature:
> http://www.enterprisedb.com/docs/en/8.3R2/perf/
Postgres_Plus_Advanced_Server_Performance_Guide-28.htm
That's a commercial version. If I was to migrate off Oracle, that will
not be to the database of some small company, it was supposed to be an
open source database. EDB does have hints, but EDB is not Postgres.
Mladen Gogala
> Update - It also appears that postgreSQL has a hint feature:
> http://www.enterprisedb.com/docs/en/8.3R2/perf/
Postgres_Plus_Advanced_Server_Performance_Guide-28.htm
>
> HTH -- Mark D Powell --
This is Postgres:
http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want
"Optimizer hints (not wanted)
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."
In essence, when I encounter a problem, I am supposed to contact one of
the gurus and have him develop a "custom patch" for me. Nice little
racket.
Thomas Kellerer
> I invested quite some time in learning the thing, as it seemed very
> similar to Oracle. Unfortunately, when it came down to the pilot project,
> the mass of the SQL to be ported ultimately required optimizer hints.
> There was simply a mass of SQL, ported to different architecture which
> Unfortunately, Postgres community is run by programming wiz kids who have
> never managed a large database and they sanctimoniously refuse to even
> consider hints.
> Consequently, I have to kill the pilot project. Postgres is, simply, not
> a viable alternative. Not with that attitude, anyway.
--> Kill file
Mladen Gogala
> Seriously though, is this a fair comparison?
No. It never was. It was never a question whether Postgres is in the same
league as Oracle, the question was whether Postgres was good enough. For
the applications developed from scratch, it might be. For the porting
projects, it's definitely no good without the optimizer hints. You are
required to either go commercial with EnterpriseDB or get one of the
"gurus' involved, as a consulting gig. That's a very cozy situation,
right there, for everybody involved. Consequently, Postgres is the only
major database that distrusts its own users so deeply to even pronounce a
fatwa against them on the official wiki.
PostgreSQL does have sort of hints but they are ugly, inefficient and
simply not enough for the large porting projects with a ton of SQL and a
deadline. All thy hope abandon. I am adopting wait and see attitude to
see if there will be a fork of the project or a new contender. Postgres
is no good for migrating from Oracle.
joel garry
I've long maintained that people underestimate the costs of open
source, even when it was "download this spreadsheet application from
DEC."
Most programming methodologies are biased towards new projects. Most
migrations are cat stampedes, too. I've never been convinced that
there is any kind of project management that can handle a migration
with a tight deadline. It's closer to winemaking than anything else -
you can go for Thunderbird (there's some queasy high school memories),
or sell none before its time.
jg
--
@home.com is bogus.
http://www.financialexpress.com/news/rs-526cr-suit-slapped-on-oracle-fin/745754/
Mladen Gogala
> I've long maintained that people underestimate the costs of open source,
> even when it was "download this spreadsheet application from DEC."
I should have had a deeper understanding of the phrase "don't
misunderestimate me" by now.
Hans Castorp
> I invested quite some time in learning the thing, as it seemed very
> similar to Oracle. Unfortunately, when it came down to the pilot project,
> the mass of the SQL to be ported ultimately required optimizer hints.
> There was simply a mass of SQL, ported to different architecture which
> Unfortunately, Postgres community is run by programming wiz kids who have
> never managed a large database and they sanctimoniously refuse to even
> consider hints.
> Consequently, I have to kill the pilot project. Postgres is, simply, not
> a viable alternative. Not with that attitude, anyway.
>
I have been developing applications backed by DBMS for 20 years now, and I never had the need to use hints (neither on Oracle nor on any other DBMS like rdb/VMS, DB2, SQL Server or Postgres). Hints have always been an absolute no-go and no SQL statement would have passed our quality controls that would only run with hints enabled (except for maybe a total of 10 statements in all those years)
If the only way you can get your stuff to run are hints, then you have some serious flaws in your application and DB design.
You should better fix _your_ application than insulting the developers of the Postgres team.
The absolutely inacceptable rude tone with which you have been ranting about this in the Postgres mailing list makes me think it's not that bad at all that you are no longer a member of the Postgres community.
Mladen Gogala
> I have been developing applications backed by DBMS for 20 years now, and
And I have been a DBA for more than that.
> I never had the need to use hints (neither on Oracle nor on any other
> DBMS like rdb/VMS, DB2, SQL Server or Postgres). Hints have always been
> an absolute no-go and no SQL statement would have passed our quality
> controls that would only run with hints enabled (except for maybe a
> total of 10 statements in all those years)
First of all, this is demonstrably false idea. Optimizer, no matter how
smart it might be, cannot help you with the correlated data. When you
have the data with an intricate correlation, like the date of birth and
Zodiac sign, to borrow Jonathan Lewis example, optimizer cannot know the
right way of doing things. Oracle allows creating extended statistics,
but even those will sometimes fail miserably with things like semijoin.
In the situation when there is correlated data, the optimizer can be
counted on to do exactly the wrong thing, no matter what the database is.
In that case, the only solution is a human directive to the optimizer.
That is what hints are.
>
> If the only way you can get your stuff to run are hints, then you have
> some serious flaws in your application and DB design.
Or, it may be a porting project, done for a different database? Are you
willing to come over here and define the budget and the resources for the
project that should be done right? I'm sure that our CIO and CFO would
love that.
> You should better
> fix _your_ application than insulting the developers of the Postgres
> team.
Postgres team is insulting its users by the deep mistrust toward them and
frequently repeated ludicrous idea that the optimizer is smarter than the
DBA. Josh Berkus, the genius behind "we don't want hints" fatwa on the Pg
wiki, has even explicitly said so.
I have wasted more than a year, learning the product, installing it,
getting it in the company and even getting the company that I work for to
host two Postgres meetings, only to be told by the designers that I am so
stupid that a computer program is smarter than me? Are you kidding me?
The designers of the product who distrust their users so deeply and
subscribe to the demonstrably false mantra that "optimizer is smarter
than the DBA" do not deserve much respect. BTW, have you considered using
the term "DBA 2.0"? Optimizer might be smarter than those guys, I give
you that.
At any rate, in my opinion porting projects from Oracle to Postgres
without hints are doomed to failure. Taking down that idiotic fatwa from
the Postgres wiki and putting hints on the ToDo list would be the first
signs that things are going the right direction. Until then, I will stay
away from Postgres.
Thomas Kellerer
> Until then, I will stay away from Postgres.
Thank you for that!
Thomas Kellerer
> First of all, this is demonstrably false idea. Optimizer, no matter how
> smart it might be, cannot help you with the correlated data. When you
> have the data with an intricate correlation, like the date of birth and
> Zodiac sign, to borrow Jonathan Lewis example, optimizer cannot know the
> right way of doing things. Oracle allows creating extended statistics,
> but even those will sometimes fail miserably with things like semijoin.
> In the situation when there is correlated data, the optimizer can be
> counted on to do exactly the wrong thing, no matter what the database is.
> In that case, the only solution is a human directive to the optimizer.
> That is what hints are.
Although I doubt you will accept the opinion of anyone else but yourself
Tom Kyte strongly detests hints as well:
From: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7038986332061
Q: When should hints be used:
A: Never. They are the path of last resort.
From: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8912905298920
"But -- I also have a rule -- don't use HINTS."
...
"If you find you are hinting every other query in your system -- something is
obviously wrong and we need to fix it. Abusing hints is not recommended, you are just
building another RBO if you do that -- precluding the software from doing its job. Might
as well not have an optimizer at that point."
I will leave this futile discussion now
Mladen Gogala
I thought you wrote "plonk"? You have problems with the Usenet filter? Do
you need hints?
Mladen Gogala
> Tom Kyte strongly detests hints as well:
>
> From:
> http://asktom.oracle.com/pls/apex/f?
p=100:11:0::::P11_QUESTION_ID:7038986332061
> Q: When should hints be used:
> A: Never. They are the path of last resort.
But they are here - as the path of the last resort.
>
>
> From:
> http://asktom.oracle.com/pls/apex/f?
p=100:11:0::::P11_QUESTION_ID:8912905298920
> "But -- I also have a rule -- don't use HINTS."
Tom is a technologist, not a DBA engaged in porting projects. Hints are
bad, I grant you that, but the only thing worse than hints themselves is
the absence of the hints when you need them. That is why every
respectable RDBMS system in the world has them. Postgres is included
here. It does have methods of controlling the optimizer, yet the
"community" hypocritically insists on the idea that "hints are bad",
instead of making them elegant and useful. In addition to that, there is
nothing here that contradicts the argument of the correlated data.
Argument of authority is a known logical fallacy and I am not sure that
Tom Kyte would like seeing his name being used for that.
> I will leave this futile discussion now
BTW, this shows the willingness to really discuss the issues. There never
was one. I was expected to bow down my head and accept the verdict of the
gurus. Sorry, not me.
Matthias Hoys
"Mladen Gogala" <n...@email.here.invalid> wrote in message
news:pan.2011.02...@email.here.invalid...
> On Fri, 04 Feb 2011 21:08:11 +0100, Thomas Kellerer wrote:
>
>> Tom Kyte strongly detests hints as well:
>>
>> From:
>> http://asktom.oracle.com/pls/apex/f?
> p=100:11:0::::P11_QUESTION_ID:7038986332061
>> Q: When should hints be used:
>> A: Never. They are the path of last resort.
>
> But they are here - as the path of the last resort.
>
>
True. And if hints don't work, try the RBO.
Matthias
Mladen Gogala
> True. And if hints don't work, try the RBO.
>
> Matthias
True, too. People on the PostgreSQL performance list told me that the
Postgres optimizer is smarter then us humans and that hints are not only
bad but also immoral and full of cholesterol. They even have a fatwa
against hints on their wiki, which I find religulous.
One of them even called Dan Tow "a fossilized DBA". He must have had a
run in with Dan, which probably didn't end up too well for him.
John Hurley
# "But -- I also have a rule -- don't use HINTS."
... Tom is a technologist, not a DBA engaged in porting projects.
Hints are bad, I grant you that, but the only thing worse than hints
themselves is the absence of the hints when you need them. That is why
every respectable RDBMS system in the world has them.
I don't remember having them or at least using them back in DB2 but it
has been a really long time.
Hints are a very mixed blessing. Having a few of them in special
circumstances well few people would argue against that.
Having them all over the place ... not in my shop thanks very much.
Having so many of them in place that they prevent a migration ...
wow. That's pretty unique I would have to think.
onedbguru
I would add my two cents about hints. Hints generally work well when
you create them. But due to changes in data patterns and volumes,
they may degrade and even more problematic, may make things worse than
before. Seen it, got the T-shirt (also had recommended against
it) . The major issue caused by this degradation is that now you
must re-code your app. Any DBA worth his salt **should** be capable of
tuning the query for optimal performance before it goes to production.
I would caveate this by saying for those one-off ad-hoc queries -
hints can get you huge performance boosts, and if it is a truely a one-
off - hint away...
Even the Oracle CBO can be a bit odd. At one time, there was a
"feature", not really a hint, but could make some queries fly.
The case was that there was a non-unique index on tablea.col3 for
example. The query even had " where tablea.col3 = 'somevalue' " and
no matter what you did, it would never use this index (or any other
index on that table - but resorted to FTS). By modifying the query
like:
where tablea.col3 = 'somevalue'
AND tablea.col3 = 'somevalue'
.....
(same values twice)
The Oracle optimizer would now give a higher ranking for tablea.col3
and - wow - look at that - an index on tablea.col3. The result would
be queries that took an hour+ to < 10-15 minutes.
For massive tables ( no 5M rows is not massive - maybe 5B+ ) parallel
hints can work wonders in Oracle. Not sure about SS,DB2 or MySQL.
John Hurley
# I have wasted more than a year, learning the product, installing it,
getting it in the company and even getting the company that I work for
to host two Postgres meetings, only to be told by the designers that I
am so stupid that a computer program is smarter than me?
I am not understanding here.
If you guys were working on a Postgres migration for a year ... I
would have guessed that early on the usage of hints in the existing
application would have been found to have been a critical factor in
evaluating possible migration paths.
Was this area just missed in an identification of risk factors?
Mladen Gogala
> If you guys were working on a Postgres migration for a year ... I would
> have guessed that early on the usage of hints in the existing
> application would have been found to have been a critical factor in
> evaluating possible migration paths.
Not only on migration. There were several projects. One was a COTS
package called Testtrack by Seapine Software, another one was a little DW
project, with a PHP front end and the migration came last. The migration
was what failed miserably. I killed the project today. To tell the truth,
the Testtrack bug tracker works beautifully, our developers have nothing
but praises for it.
Noons
>
> I have been developing applications backed by DBMS for 20 years now, and I never
> had the need to use hints (neither on Oracle nor on any other DBMS like rdb/VMS,
> DB2, SQL Server or Postgres). Hints have always been an absolute no-go and no
> SQL statement would have passed our quality controls that would only run with
> hints enabled (except for maybe a total of 10 statements in all those years)
>
> If the only way you can get your stuff to run are hints, then you have some
> serious flaws in your application and DB design.
> You should better fix _your_ application than insulting the developers of the
> Postgres team.
>
> The absolutely inacceptable rude tone with which you have been ranting about
> this in the Postgres mailing list makes me think it's not that bad at all that
> you are no longer a member of the Postgres community.
>
And that, folks, is an example of a perfect idiot.
Funny thing is he admits being so for 20 years...
Noons
> "Optimizer hints (not wanted)
>
> Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues.
Beg your pardon? So, a hint that fixes a plan "introduces upgrade and
maintenance issues", but a patch to fix a specific problem with the optimizer
does not introduce such?
Hellooooooo?
Has any of these idiots EVER had to upgrade an existing production system? Ah
yes, I forgot this is the open community: they don't give a fig about existing
production, they just "rework" or "refactor" whenever needed.
Nice racket if you can make it stick...
> 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."
Ah, ok: the "too hard basket". Of course. IOW: we'll find a sucker to go and
investigate that one of these days.
> In essence, when I encounter a problem, I am supposed to contact one of
> the gurus and have him develop a "custom patch" for me. Nice little
> racket.
LOL! Narh, perish the thought!
As if that hasn't been the MO of that lot since day one...
Eric
I don't get it either. You've been playing with Postgres for a year,
expecting to do migrations, and you didn't do trial migrations early on,
basically at the proof-of-concept stage?
Then you expect the Postgres team to re-open an issue they have already
discussed many times. When was the last time you tried to get Oracle to
change a technology/policy issue? How did you get on?
Also, you are expecting a different product to solve an issue in the
same way as the product you are used to. You can't ever do that for all
aspects of a product, you will always have to take one or more steps back
through a problem to see that there is a totally different solution in
the new product.
And you have no idea how to influence an open-source project.
Eric
Jonathan Lewis
"Thomas Kellerer" <OTPXDA...@spammotel.com> wrote in message
news:8r34l6...@mid.individual.net...
>
> Tom Kyte strongly detests hints as well:
>
> From:
> http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7038986332061
> Q: When should hints be used:
> A: Never. They are the path of last resort.
>
"Never" and "last resort" aren't mutually compatible.
I think we can infer from this that "never" is an exaggeration for emphsis
>
> From:
> http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8912905298920
> "But -- I also have a rule -- don't use HINTS."
> ...
But the next sentence lists some hints that Tom thinks are okay - so
there's another citation that isn't the black and white decision that you
imply..
> "If you find you are hinting every other query in your system --
> something is
> obviously wrong and we need to fix it. Abusing hints is not recommended,
> you are just
> building another RBO if you do that -- precluding the software from doing
> its job. Might
> as well not have an optimizer at that point."
>
"every other query", "abusing hints" - I think we get the idea from this
that Tom's complain matches the fears of the Postgres developers, that if
you supply a feature some people will abuse it (as in "abusing hints"),
rather than using it with the caution it merits.
>
> I will leave this futile discussion now
>
Before you go, could you tell us what you would have to do as a DBA to
ensure that Postgres could calculate the correct selectivity on something
as simple as:
select *
from orders
where
date_delivered > date_placed + 72 hours
;
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
John Hurley
# Q: When should hints be used:
... A: Never. They are the path of last resort.
JL: "Never" and "last resort" aren't mutually compatible.
Too bad Tom so rarely jumps back into cdos these days.
Easy to play around with different spins here ... the first word
sentence that he gives back in his answer here sounds like the TK we
know.
Triggers are evil!
Mladen Gogala
> Triggers are evil!
>
Yes they are evil. I saw more then one trigger which badly messed things
up. Triggers are also necessary for implementing business logic.
Mladen Gogala
> I don't get it either. You've been playing with Postgres for a year,
> expecting to do migrations, and you didn't do trial migrations early on,
> basically at the proof-of-concept stage?
The whole thing was a proof of concept. There were 2 pilot projects that
I did, before the machine was allocated. The idea was something like:
"Mladen, see if we can use Postgres to replace some of our Oracle
databases". Of course, I had to learn a new database system, which I did.
That doesn't happen over night. My judgment, delivered yesterday, was:
'Postgres cannot be used for mission critical applications. It can be
used for replacing small, ancillary databases and used to store for LDAP
data, but much more. It lacks necessary features and switching one of the
mission critical applications to Postgres would jeopardize our business".
Basically, Postgres is just a toy. That's exactly what Mr. Berkus wrote
about it, in his naivety.
Mladen Gogala
> Beg your pardon? So, a hint that fixes a plan "introduces upgrade and
> maintenance issues", but a patch to fix a specific problem with the
> optimizer does not introduce such?
> Hellooooooo?
I posted that here because Postgres has recently been advertised as an
alternative to Oracle. It's nothing of the kind and the people ogling it
should be aware of that. It was foolish of me to expect otherwise. It was
an off topic post which otherwise doesn't belong on this group, but that
was the reason for me to put it here.
Noons
>
>> Triggers are evil!
>>
>
> Yes they are evil. I saw more then one trigger which badly messed things
> up. Triggers are also necessary for implementing business logic.
Humm, that is one of the very few times I totally disagree with TK.
Not all triggers are evil.
Data level triggers are definitely in that class - *if* used irresponsibly.
Database level triggers are very convenient and necessary in many cases.
Given the purpose and objective of each is completely different and addresses
completely different needs, would it hurt TK too much to qualify that statement?
Otherwise we can also state that
"SQL is evil"
because plenty of folks can provide examples of such and therefore "prove" it.
Blanket statements with no context qualification whatsoever serve no purpose.
Otherwise we're gonna end up with another "rebuild indexes is evil" crusade,
which really does nothing else than sell books and consultancy...
Noons
It's good to have this sort of thing exposed. These morons rely too much on
their little forums where everyone jumps the same way when the whistle sounds.
They need to be shown clearly that they are a vast minority, unlike what their
cozy little corners lead them to believe.
prunoki
Hmmm, not giving me the tools to let me solve my own problems in the
way I want seems like a bad idea. And yes, migration projects can be
extremely tricky that way. There is time for evil and dirty to make it
work and there is time to remove them later.
Mladen Gogala
There are two excellent papers here:
http://www.singingsql.com/WhyGoodOptimizersDoNeedaHintLongerVersion.htm
http://www.nocoug.org/download/2005-08/
WhenGoodOptimizersMakeBadChoices.doc
Both were written by Dan Tow, a long term DBA who has my utmost respect.
The papers pre-date this debate for several years and present a very
coherent argument in support of hints.
They do not cover the psychological side of things. Telling users that
PostgreSQL creators consider them dumber than a computer program is
simply not a good idea if PostgreSQL is supposed to be an alternative to
Oracle. It isn't. I am glad that we've ironed that out.
Matthias Hoys
"Noons" <wizo...@yahoo.com.au> wrote in message
news:iilcre$mlt$1...@news.eternal-september.org...
Relational databases are evil. Let's go NoSQL :-)
Mladen Gogala
> Relational databases are evil. Let's go NoSQL
>
Actually, there is a hilarious clip on YouTube called "MongoDB is web
scale". This clip intrigued me so I installed MongoDB and was pleasantly
surprised. It is quite a nice piece of software and will be very well
suited for a document database once they iron out few critical bugs.
However, the spreadsheet/table way of representing things is deeply woven
into the way companies are doing business and will not go away anytime
soon. One of the advantages of RDBMS systems is precisely the fact that
they impose structure on the otherwise chaotic set of business data.
Being structureless is not necessarily an advantage.
Having said that, I believe that MongoDB will survive, as a niche player,
and may actually become quite large. Problem, however, with many open
source projects, is that they are frequently overrun by hippies and adopt
some religious beliefs which can eventually sink them or marginalize
them. Examples of such projects are Pascal, Modula-2, ogg and some
others. Smart thing on the part of Linus Torvalds is precisely the fact
that he didn't allow that.
Noons
Exactly!
¦D
Noons
> and may actually become quite large. Problem, however, with many open
> source projects, is that they are frequently overrun by hippies and adopt
> some religious beliefs which can eventually sink them or marginalize
> them.
Please! Let's not insult the hippies!
<g,d&r>
> Examples of such projects are Pascal, Modula-2, ogg and some
> others.
Put Gimp in that group. At one stage, it was a credible alternative to much
more expensive editing software. Unfortunately, it's been turned into a joke...
> Smart thing on the part of Linus Torvalds is precisely the fact
> that he didn't allow that.
Is he still relevant nowadays?
Noons
>> It's good to have this sort of thing exposed. These morons rely too much on
>> their little forums where everyone jumps the same way when the whistle sounds.
>> They need to be shown clearly that they are a vast minority, unlike what their
>> cozy little corners lead them to believe.
>
> Hmmm, not giving me the tools to let me solve my own problems in the
> way I want seems like a bad idea. And yes, migration projects can be
> extremely tricky that way. There is time for evil and dirty to make it
> work and there is time to remove them later.
>
Absolutely! Look at the arguments these bozos put forward.
"dbas should fix their apps instead of fiddling with sql hints"!
Conveniently ignoring and bypassing that:
1- dbas do NOT own apps. Bozo duhvelopers do.
2- dbas do NOT fix apps. Bozo duhvelopers do.
3- dbas are charged with running an app in a reliable/stable fashion.
Impossible to do when the software written by bozos is so unstable.
"if your app sql needs hints to run, you should re-write/re-design it"
Conveniently ignoring and bypasing that:
1- dbas do NOT write app sql. Bozo duhvelopers do.
2- dbas do not design app code and its sql. Bozo duhvelopers do.
"only 0.1% of SQL needs hints"
Totally wrong. As usual: a figure plucked from their derrieres, with no context
or even any true base, simply designed to elicit an emotive response.
Far from it. It's more like 0.001%!
Case in point: payroll app from a well known maker. 90000 SQL statements, of
which one (1!!!) sql statement causes payroll calculation runs to blow out to 30
hours instead of the usual 5 minutes.
That's around 0.0011% of the sql. Not 0.1%
Now, go and explain to the 2500 employees waiting for their pay that you will
not fix the problem because hinting that 1 statement is "wrong" and instead you
want to wait for the app duhveloper to patch it.
Can these idiots even grasp how ignorant and biased they sound?
But of course it's the "dba's fault". And this from morons who are supposed to
be responsible for a product...
Mladen Gogala
> Hmmm, not giving me the tools to let me solve my own problems in the way
> I want seems like a bad idea. And yes, migration projects can be
> extremely tricky that way. There is time for evil and dirty to make it
> work and there is time to remove them later.
This would be a very promising database, if those cognitively challenged
guys hadn't taken it over. I would even like to start a fork but I am not
a programmer. If someone is interested and knows how to do that, let me
know. Time to re-read my Kernighan and Ritchie.
ddf
> On Feb 4, 6:05 pm, Mladen Gogala <n...@email.here.invalid> wrote:
>
> > On Fri, 04 Feb 2011 23:50:30 +0100, Matthias Hoys wrote:
> > > True. And if hints don't work, try the RBO.
>
> > > Matthias
>
> > True, too. People on the PostgreSQL performance list told me that the
> > Postgres optimizer is smarter then us humans and that hints are not only
> > bad but also immoral and full of cholesterol. They even have a fatwa
> > against hints on their wiki, which I find religulous.
> > One of them even called Dan Tow "a fossilized DBA". He must have had a
> > run in with Dan, which probably didn't end up too well for him.
>
> > --http://mgogala.byethost5.com
>
> I would add my two cents about hints. Hints generally work well when
> you create them. But due to changes in data patterns and volumes,
> they may degrade and even more problematic, may make things worse than
> before. Seen it, got the T-shirt (also had recommended against
> it) . The major issue caused by this degradation is that now you
> must re-code your app. Any DBA worth his salt **should** be capable of
> tuning the query for optimal performance before it goes to production.
>
And I agree but many DBAs don't have the luxury of tuning queries
before they get to production as the companies they work for use
canned apps where the code cannot be touched. Hints and outlines can
save the day in such situations.
> I would caveate this by saying for those one-off ad-hoc queries -
> hints can get you huge performance boosts, and if it is a truely a one-
> off - hint away...
>
Not for prepackaged code that is 'untouchable'. Or don't you have to
administer systems like Portal?
> Even the Oracle CBO can be a bit odd. At one time, there was a
> "feature", not really a hint, but could make some queries fly.
>
> The case was that there was a non-unique index on tablea.col3 for
> example. The query even had " where tablea.col3 = 'somevalue' " and
> no matter what you did, it would never use this index (or any other
> index on that table - but resorted to FTS). By modifying the query
> like:
>
> where tablea.col3 = 'somevalue'
> AND tablea.col3 = 'somevalue'
> .....
> (same values twice)
>
> The Oracle optimizer would now give a higher ranking for tablea.col3
> and - wow - look at that - an index on tablea.col3. The result would
> be queries that took an hour+ to < 10-15 minutes.
>
> For massive tables ( no 5M rows is not massive - maybe 5B+ ) parallel
> hints can work wonders in Oracle. Not sure about SS,DB2 or MySQL.
David Fitzjarrell
joel garry
> Mladen Gogala wrote,on my timestamp of 5/02/2011 1:58 AM:
>
> > "Optimizer hints (not wanted)
>
> > Optimizer hints are used to work around problems in the optimizer and
> > introduce upgrade and maintenance issues.
>
> Beg your pardon? So, a hint that fixes a plan "introduces upgrade and
> maintenance issues", but a patch to fix a specific problem with the optimizer
> does not introduce such?
> Hellooooooo?
I think the relevant problem is reliance on hints. Tom Kyte's real
feeling about hints is "See Jonathan:" http://tkyte.blogspot.com/2006/08/words-of-wisdom.htm
I think we all knew that, but it's always fun to pick on noteworthy
absolute statements.
>
> Has any of these idiots EVER had to upgrade an existing production system? Ah
> yes, I forgot this is the open community: they don't give a fig about existing
> production, they just "rework" or "refactor" whenever needed.
> Nice racket if you can make it stick...
>
> > 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."
>
> Ah, ok: the "too hard basket". Of course. IOW: we'll find a sucker to go and
> investigate that one of these days.
>
> > In essence, when I encounter a problem, I am supposed to contact one of
> > the gurus and have him develop a "custom patch" for me. Nice little
> > racket.
>
> LOL! Narh, perish the thought!
> As if that hasn't been the MO of that lot since day one...
I may have missed something, but isn't the enterprisedb product
supposed to address this?
http://www.enterprisedb.com/docs/en/8.3/oracompat/EnterpriseDB_OraCompat_EN_8.3-92.htm#P4180_251202
I get what Mladen is saying about the developers and the racket - I'd
even say it derives straight from the bazaar doc - I get what you are
saying about "that lot," but I don't quite get why it doesn't count
that a commercial solution for the exact problem exists. Fast, good,
cheap, pick two, you know?
jg
--
@home.com is bogus.
http://arnoldzwicky.wordpress.com/category/linguistics-in-the-comics/
Peter Schneider
ROTFL :-))
I'd say that if you really need to re-read K&R, if you are not able to
re-write it from the top of your head... Well that's some impressive starter
to fork PostgreSQL :-)) I'd say there is no way you can go... But that's just
me... just go ahead!
SCNR
Regards
Peter
--
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain
Noons
>> Beg your pardon? So, a hint that fixes a plan "introduces upgrade and
>> maintenance issues", but a patch to fix a specific problem with the optimizer
>> does not introduce such?
>> Hellooooooo?
>
> I think the relevant problem is reliance on hints.
That's not a problem. What is a problem is over-reliance on hints. I *do* rely
on hints to fix problems I cannot possibly fix otherwise. *Then* - and only
then - they are simply impossible to live without.
Unless of course we are in one of the sites these bozos populate, where
apparently access to the duhvelopers who wrote the app is simple and immediate.
How many of such have you been to in the last 10 years?
Thought so...
> Tom Kyte's real
> feeling about hints is "See Jonathan:" http://tkyte.blogspot.com/2006/08/words-of-wisdom.htm
> I think we all knew that, but it's always fun to pick on noteworthy
> absolute statements.
But that's exactly the problem here: reliance on noteworthy absolute statements.
If there is one thing I've learned in all these years is that there is no such
thing as absolutes. Hence why so many folks use that (awful) expression - "it
depends" - whenever someone asks any question a bit out of the "A,B,C...".
One of the problems with such absolute statements is they get picked up by this
kind of bozo and get used as justification for their deranged conclusions.
Kinda like "we don't need no dbas", really.
> I may have missed something, but isn't the enterprisedb product
> supposed to address this?
> http://www.enterprisedb.com/docs/en/8.3/oracompat/EnterpriseDB_OraCompat_EN_8.3-92.htm#P4180_251202
Likely. Sorry, not familiar.
> I get what Mladen is saying about the developers and the racket - I'd
> even say it derives straight from the bazaar doc - I get what you are
> saying about "that lot," but I don't quite get why it doesn't count
> that a commercial solution for the exact problem exists. Fast, good,
> cheap, pick two, you know?
Very true.
Noons
>
> And I agree but many DBAs don't have the luxury of tuning queries
> before they get to production as the companies they work for use
> canned apps where the code cannot be touched. Hints and outlines can
> save the day in such situations.
"many"? Where I live, it's almost all of them. Hence why generalizations of
the kind "dbas like hints because they lock-in" are completely out of kilter and
void of any context or value.
> Not for prepackaged code that is 'untouchable'. Or don't you have to
> administer systems like Portal?
Yeah. But for example, execution profiles and such are simply impossible
without some hint mechanism. These luminaries have basically brain-damaged
Postgres for large production environments because they didn't ask any
experienced production dba how to handle third party apps and what typical
problems they see.
But of course we know they are all geniuses and any field experience for them is
flawed by "dba attitudes". And other such lunacy. Typical Stonebraker attitude.
Mladen Gogala
> But of course we know they are all geniuses and any field experience for
> them is flawed by "dba attitudes". And other such lunacy. Typical
> Stonebraker attitude.
I decided to write the hint system myself. Given the current state of my
projects, I am in the middle of writing another PHP book, it will take me
a few months, maybe even a year, but I will do it. If that doesn't get
entered into the code, I will start working on a fork.
Stonebraker lost to Oracle precisely because of his attitude.
Unfortunately, he has passed that "artistic" attitude down to his heirs.
I was wondering why Oracle didn't go after Postgres and it did go after
MySQL and I figured it out. Postgres is, essentially, a harmless artistic
project which doesn't stand much chance of ever taking hold in the
corporate server rooms. They experienced a surge of interest with the
raising prices of Oracle, but it will pass. For that project to really be
successful, it would have to be wrestled out of the hands of the former
pastry bakers and their ilk.
Fred Pierce
wrote:
I keep having flashbacks to the no outer joins no nulls ever Ingres
days.
John Hurley
# Unless of course we are in one of the sites these bozos populate,
where apparently access to the duhvelopers who wrote the app is simple
and immediate.
Some of do live and work in environments where in house custom
applications are written and maintained.
It probably takes a fair amount of being in the right place at the
right time to find those opportunities.
joel garry
Now, that is beyond totally cool!
I sure hope this is one of those times a dedicated person or small
group blows past the "experts." As down as I can be on DIY for the
commercial world, I have seen it work. A necessary condition is one
person with the motivation, qualification and resources to pull it
off.
You go, Gogala!
jg
--
@home.com is bogus.
http://it.toolbox.com/blogs/oracle-guide/enterprisedb-innodb-and-oracle-6192
Jonathan Lewis
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:qpudnSPoRugqwdDQ...@bt.com...
>
> "Thomas Kellerer" <OTPXDA...@spammotel.com> wrote in message
> news:8r34l6...@mid.individual.net...
>>
>>
>
> Before you go, could you tell us what you would have to do as a DBA to
> ensure that Postgres could calculate the correct selectivity on something
> as simple as:
>
> select *
> from orders
> where
> date_delivered > date_placed + 72 hours
> ;
>
I don't suppose there's anyone from the Postgress optimizer programmers
around still to supply an answer to this question.
If this one is too easy, I'm ready with several that gradually get harder.
Regards
Jonathan Lewis
Noons
>
> >But of course we know they are all geniuses and any field experience for them is
> >flawed by "dba attitudes". And other such lunacy. Typical Stonebraker attitude.
>
> I keep having flashbacks to the no outer joins no nulls ever Ingres
> days.
Bingo...
Noons
wrote:
>
> > Before you go, could you tell us what you would have to do as a DBA to
> > ensure that Postgres could calculate the correct selectivity on something
> > as simple as:
>
> > select *
> > from orders
> > where
> > date_delivered > date_placed + 72 hours
> > ;
>
> I don't suppose there's anyone from the Postgress optimizer programmers
> around still to supply an answer to this question.
> If this one is too easy, I'm ready with several that gradually get harder.
>
Don't get your hopes too high, you know that's just "dba nonsense":
anything out of their mindset can't possibly exist or have any
relevance.
Small details like large production environments are "impure" and only
"pollute the thought process"...
Tuomas
> Mladen Gogala wrote,on my timestamp of 7/02/2011 9:28 AM:
> > Smart thing on the part of Linus Torvalds is precisely the fact
>> that he didn't allow that.
>
> Is he still relevant nowadays?
As far as I know everything going into Linux kernel goes through Linus.
I really can't say if that's "being relevant" or not, but I'd vote "yes".
On topic:
I'd like to compare hints to gotos, no-one really wants them and too many is
a nightmare, but sometimes they are really, really useful.
Also: "Never" is a very long time, I suspect religious fanatism(*) every
time I hear it. ;)
"Never" as in "never in the lifetime of this application" might even be
possible.
*) As we know, reality or facts have no effect on religion.
--
Tuomas - VWs:'63 typ14, '65 typ34 & '61 typ2
Mladen Gogala
> On topic:
>
> I'd like to compare hints to gotos, no-one really wants them and too
> many is a nightmare, but sometimes they are really, really useful.
Great comparison. I like it very much.
joel garry
> On Fri, 11 Feb 2011 14:07:58 +0200, Tuomas wrote:
> > On topic:
>
> > I'd like to compare hints to gotos, no-one really wants them and too
> > many is a nightmare, but sometimes they are really, really useful.
>
> Great comparison. I like it very much.
s/goto/condom/
Back 30 years ago, when much of my work was untangling spaghetti code,
it often was useful to make the rule "no gotos, except to the end of a
loop" which is what other verbs do these days.
jg
--
@home.com is bogus.
"Friends with benefits" http://www.theregister.co.uk/2011/02/09/oracle_fujitsu_sparc_collaboration/
Noons
>>> I'd like to compare hints to gotos, no-one really wants them and too
>>> many is a nightmare, but sometimes they are really, really useful.
>>
>> Great comparison. I like it very much.
>
> s/goto/condom/
>
> Back 30 years ago, when much of my work was untangling spaghetti code,
> it often was useful to make the rule "no gotos, except to the end of a
> loop" which is what other verbs do these days.
>
and none in PL/SQL...
:(
Serge Rielau
to disect things.
The idea of SQL is that you tell the system WHAT you want and the system
figures out HOW to get it.
Now the DBMS is responsible to derive proper statistics from the data
and things like extended stats (or column group stats) have been mentioned.
In your query it is absolutely possible for the DBMS to understand the
correlation, it's just that non ethat I know of has ever bothered
implementing it.
BUT, as you say the problem here is selectivity. It not you wanting to
pick a plan. It is you wanting to hint selectivity.
For example:
SELECT * FROM emp WHERE salary > ?
Well, if the intent of the query is to find highly paid employees then
you have a certain expectation.. Perhaps:
SELECT * FROM emp WHERE salary > ? SELECTIVITY 0.02
This is way more useful than dictating the plan.
In your case WHERE date_delivered> date_placed + 72 hours SELECTIVITY 0.01
Would tell the optimizer that you expect this to be the exception and to
act accordingly.
Just some food for thought....
Serge
PS: Why the OP went to Postgress and not PostgressPlus (aka EDB) is a
mystery to me.
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau
Jonathan Lewis
news:8rp8q7...@mid.individual.net...
>
> The idea of SQL is that you tell the system WHAT you want and the system
> figures out HOW to get it.
The fundamental idea of the relational database is that you store only the
data,
and don't store "routing" information with the data. The database is only
required
to give you the right answer, relational theory says nothing about
performance,
that's an implementation detail.
> Now the DBMS is responsible to derive proper statistics from the data and
> things like extended stats (or column group stats) have been mentioned.
> In your query it is absolutely possible for the DBMS to understand the
> correlation, it's just that non ethat I know of has ever bothered
> implementing it.
Technically Oracle 8 could handle this requirement, provided the query were
rewritten in a suitable fashion. The problem isn't one of correlation, by
the way -
you could know correlation coefficient and still get a hugely misleading
estimate
of selectivity.
> BUT, as you say the problem here is selectivity. It not you wanting to
> pick a plan. It is you wanting to hint selectivity.
But the "best plan" cannot be derived without the ability to calculate the
correct
selectivity. This was simplifying the problem.
>
> In your case WHERE date_delivered> date_placed + 72 hours SELECTIVITY
> 0.01
> Would tell the optimizer that you expect this to be the exception and to
> act accordingly.
>
Which brings us back to the need for hints even for very simple queries -
when my
question was: "how do you do it without hinting".
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Serge Rielau
> Which brings us back to the need for hints even for very simple queries -
> when my
> question was: "how do you do it without hinting".
My point is that the hint should supply the missing information (e.g.
selectivity) and not dictate the plan.
It's still a hint.
BTW, the IBM position (which may or may not match mine) is that the user
tells support (i.e. last resort) that they are stuck and support
provides the hint (i.e. no need to wait for a patch).
If hints are truly rarely needed then this ought to be an acceptable
compromise between idealism and pragmatism.
Cheers
Serge
Mladen Gogala
> BTW, the IBM position (which may or may not match mine) is that the user
> tells support (i.e. last resort) that they are stuck and support
> provides the hint (i.e. no need to wait for a patch). If hints are truly
> rarely needed then this ought to be an acceptable compromise between
> idealism and pragmatism.
That is true, that's why DB2 supports hints, in the first place:
http://tinyurl.com/48fv7w7
Hints are documented, so the users can use them if they think that hints
are necessary. Nice thing to know that support also advises the use of
hints.
Noons
> BTW, the IBM position (which may or may not match mine) is that the user tells
> support (i.e. last resort) that they are stuck and support provides the hint
> (i.e. no need to wait for a patch).
> If hints are truly rarely needed then this ought to be an acceptable compromise
> between idealism and pragmatism.
And that is perfectly acceptable and makes a lot of sense.
Jonathan Lewis
> On 2/13/2011 7:51 AM, Jonathan Lewis wrote:
>> Which brings us back to the need for hints even for very simple
>> queries -
>> when my
>> question was: "how do you do it without hinting".
> I don't think we differ conceptually.
> My point is that the hint should supply the missing information (e.g.
> selectivity) and not dictate the plan.
> It's still a hint.
>
Fair enough, but my comment was addressed to the argument that "the
optimizer doesn't need hints".
Mind you, the "missing information" is a lot harder than your response to
my first trivial example.
Take a 4 table join, with simple filter predicates and join predicates.
You may be able to say
selectivity(table1 xxx) ... selectivity(table4 yyyy)
but then you need to be able to say
selectivity(table1,table2 nnnn) - because you know it's much smaller
than the optimizer things
but you also may have to say
selectivity(table1,table3 mmmm)
selectivity(table1,table4 mmmm)
because you know those are much larger than the optimizer things
then you have to worry about three table selectivities to stop the picking
the wrong third table .. and so on.
Or you might decide that you need to be able to say:
selectivity (t1, t2, based on colX = coly, selectivity) -- or something
similar.
Ultimately it's much harder (and much less reasonable) for the DBA to
decide what the right information is for a particular query than it is to
know enough to determine a sensible path.
joel garry
wrote:
>
> Ultimately it's much harder (and much less reasonable) for the DBA to
> decide what the right information is for a particular query than it is to
> know enough to determine a sensible path.
>
I don't know if people outside of the US are following it, but IBM's
Watson computer is playing on Jeopardy. It's kind of an infomercial
for IBM, not that that is a bad thing. Big roomful of servers, with
an icon on a screen in the player's area showing how much it is
"sweating." Last night's first round ended in a tie between Watson
and one of the humans.
The funniest part was when Watson said "Harry Potter" when the correct
question was "Voldemort." 2880 POWER7 processor cores and 16
Terabytes of RAM, got it exactly backwards in 3 seconds.
jg
--
@home.com is bogus.
Mladen Gogala
> The funniest part was when Watson said "Harry Potter" when the correct
> question was "Voldemort." 2880 POWER7 processor cores and 16 Terabytes
> of RAM, got it exactly backwards in 3 seconds.
It's magic.