Re: [PERFORM] Hints proposal - Mailing list pgsql-hackers

From Csaba Nagy
Subject Re: [PERFORM] Hints proposal
Date
Msg-id 1160672686.28751.76.camel@coppola.muc.ecircle.de
Whole thread Raw
In response to Re: [PERFORM] Hints proposal  (Josh Berkus <josh@agliodbs.com>)
List pgsql-hackers
> Hmmm, if you already understand Visual Basic syntax, should we support
> that too?  Or maybe we should support MySQL's use of '0000-00-00' as the
> "zero" date because people "understand" that?

You completely misunderstood me... I have no idea about oracle hints,
never used Oracle in fact. My company uses oracle, but I have only very
very limited contact with oracle issues, and never touched a hint.

I'm only talking about ease of use, learning curves, and complexity in
general. While I do like the idea of an all automatic system optimizer
which takes your query portofolio and analyzes the data based on those
queries and creates you all the indexes you need and all that, that's
not gonna happen soon, because it's a very complex thing to implement.

The alternative is that you take your query portofolio, analyze it
yourself, figure out what statistics you need, create indexes, tweak
queries, hint the planner for correlations and stuff... which is a
complex task, and if you have to tell the server about some correlations
with the phase of the moon, you're screwed cause there will never be any
DB engine which will understand that.

But you always can put the corresponding hint in the query when you know
the correlation is there...

The problem is that the application sometimes really knows better than
the server, when the correlations are not standard.

> We're just not going to adopt a bad design because Oracle DBAs are used
> to it.   If we wanted to do that, we could shut down the project and
> join a proprietary DB staff.

I have really nothing to do with Oracle. I think you guys are simply too
blinded by Oracle hate... I don't care about Oracle.

> The current discussion is:
>
> a) Planner tweaking is sometimes necessary;
> b) Oracle HINTS are a bad design for planner tweaking;

While there are plenty of arguments you made against query level hints
(can we not call them Oracle-hints ?), there are plenty of users of
postgres who expressed they would like them. I guess they were tweaking
postgres installations when they needed it, and not Oracle
installations. I expressed it clearly that for me query level hinting
would give more control and better understanding of what I have to do
for the desired result. Perfect planning -> forget it, I only care about
good enough with reasonable tuning effort. If I have to tweak statistics
I will NEVER be sure postgres will not backfire on me again. On the
other hand if I say never do a seq scan on this table for this query, I
could be sure it won't...

> c) Can we come up with a good design for planner tweaking?

Angles again: good enough now is better for end users, but programmers
always go for perfect tomorrow... pity.

Cheers,
Csaba.



pgsql-hackers by date:

Previous
From: Josh Berkus
Date:
Subject: Re: [PERFORM] Hints proposal
Next
From: Tom Lane
Date:
Subject: Re: Getting the type Oid in a CREATE TYPE output function