From: Arjen van der Meijden <acmmailing@tweakers.net>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Csaba Nagy <nagy@ecircle-ag.com>
Cc: Jim C. Nasby <jim@nasby.net>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Hints proposal
Date: Thu, 12 Oct 2006 23:07:12 +0200
Message-ID: <452EAE80.5030501@tweakers.net> (raw)
In-Reply-To: <1160680031.31966.128.camel@dogma.v10.wvs>
References: <20061012151439.GT28647@nasby.net>
<1160671375.31966.84.camel@dogma.v10.wvs>
<1160673340.28751.83.camel@coppola.muc.ecircle.de>
<1160680031.31966.128.camel@dogma.v10.wvs>
On 12-10-2006 21:07 Jeff Davis wrote:
> On Thu, 2006-10-12 at 19:15 +0200, Csaba Nagy wrote:
>
> 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.
There are some disadvantages of not writing the hints in a query. But of
course there are disadvantages to do as well ;)
One I can think of is that it can be very hard to define which hint
should apply where. Especially in complex queries, defining at which
point exaclty you'd like your hint to work is not a simple matter,
unless you can just place a comment right at that position.
Say you have a complex query with several joins of the same table. And
in all but one of those joins postgresql actually chooses the best
option, but somehow you keep getting some form of join while a nested
loop would be best. How would you pinpoint just that specific clause,
while the others remain "unhinted" ?
Your approach seems to be a bit similar to aspect oriented programming
(in java for instance). You may need a large amount of information about
the queries and it is likely a "general" regexp with "general" hint will
not do much good (at least I expect a hinting-system to be only useable
in corner cases and very specific points in a query).
By the way, wouldn't it be possible if the planner learned from a query
execution, so it would know if a choice for a specific plan or estimate
was actually correct or not for future reference? Or is that in the line
of DB2's complexity and a very hard problem and/or would it add too much
overhead?
Best regards,
Arjen
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: acmmailing@tweakers.net, pgsql@j-davis.com, nagy@ecircle-ag.com, jim@nasby.net
Subject: Re: Hints proposal
In-Reply-To: <452EAE80.5030501@tweakers.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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