Re: Hints proposal - Mailing list pgsql-performance

On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote:
> > I'm not suggesting that we do that, but it seems better then embedding
> > the hints in the queries themselves.
>
> OK, what about this: if I execute the same query from a web client, I
> want the not-so-optimal-but-safe plan, if I execute it asynchronously, I
> let the planner choose the
> best-overall-performance-but-sometimes-may-be-slow plan ?
>

Connect as a different user to control whether the hint matches or not.
If this doesn't work for you, read below.

> What kind of statistics/table level hinting will get you this ?
>

It's based not just on the table, but on environment as well, such as
the user/role.

> I would say only query level hinting will buy you query level control.
> And that's perfectly good in some situations.

My particular proposal allows arbitrary regexes on the raw query. You
could add a comment with a "query id" in it.

My proposal has these advantages over query comments:
(1) Most people's needs would be solved by just matching the query
form.
(2) If the DBA really wanted to separate out queries individually (not
based on the query form), he could do it, but it would have an extra
step that might encourage him to reconsider the necessity
(3) If someone went to all that work to shoot themselves in the foot
with unmanagable hints that are way too specific, the postgres
developers are unlikely to be blamed
(4) No backwards compatibility issues that I can see, aside from people
making their own hints unmanagable. If someone started getting bad
plans, they could just remove all the hints from the system catalogs and
it would be just as if they had never used hints. If they added ugly
comments to their queries it wouldn't really have a bad effect.

To formalize the proposal a litte, you could have syntax like:

CREATE HINT [FOR USER username] MATCHES regex APPLY HINT some_hint;

Where "some_hint" would be a hinting language perhaps like Jim's, except
not guaranteed to be compatible between versions of PostgreSQL. The
developers could change the hinting language at every release and people
can just re-write the hints without changing their application.

> I really can't see why a query-level hinting mechanism is so evil, why
> it couldn't be kept forever, and augmented with the possibility of
> correlation hinting, or table level hinting.

Well, I wouldn't say "evil". Query hints are certainly against the
principles of a relational database, which separate the logical query
from the physical storage.

Regards,
    Jeff Davis