|
On 22/11/12 06:28, Craig James wrote:
On Wed, Nov 21, 2012 at 9:25 AM, Joe
Conway <mail@xxxxxxxxxxxxx>
wrote:
On 11/21/2012 08:05 AM, Heikki Linnakangas wrote:
> Rather than telling the planner what to do or not to do,
I'd much rather
> have hints that give the planner more information about
the tables and
> quals involved in the query. A typical source of bad
plans is when the
> planner gets its cost estimates wrong. So rather than
telling the
> planner to use a nested loop join for "a INNER JOIN b ON
a.id
= b.id",
> the user could tell the planner that there are only 10
rows that match
> the "a.id = b.id" qual. That
gives the planner the information it needs
> to choose the right plan on its own. That kind of hints
would be much
> less implementation specific and much more likely to
still be useful, or
> at least not outright counter-productive, in a future
version with a
> smarter planner.
>
> You could also attach that kind of hints to tables and
columns, which
> would be more portable and nicer than decorating all
queries.
I like this idea, but also think that if we have a syntax to
allow
hints, it would be nice to have a simple way to ignore all
hints (yes, I
suppose I'm suggesting yet another GUC). That way after
sprinkling your
SQL with hints, you could easily periodically (e.g. after a
Postgres
upgrade) test what would happen if the hints were removed.
Or a three-way choice: Allow, ignore, or generate an error.
That would allow developers to identify where hints are being
used.
Craig
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
Or perhaps hints should have the pg version attached, so that they
are automatically ignored when the pg version changed? Problem may
then become people reluctant to upgrade because their hints relate
to a previous version! Sigh...
Even requiring registration of hints and expiring them after a
limited time period would not work - as people would simply automate
the process of registration & application...
Cheers,
Gavin
|
