From: Shaun Thomas <sthomas@leapfrogonline.com>
To: pgsql-performance@postgresql.org
Subject: Re: Hints proposal
Date: Mon, 16 Oct 2006 12:00:01 -0500
Message-ID: <200610161200.01913.sthomas@leapfrogonline.com> (raw)
In-Reply-To: <4533A6F7.7010708@janestcapital.com>
References: <78ED28FACE63744386D68D8A9D1CF5D4209CBF@MAIL.corp.lumeta.com>
<1161005266.28751.145.camel@coppola.muc.ecircle.de>
<4533A6F7.7010708@janestcapital.com>
On Monday 16 October 2006 10:36, Brian Hurt wrote:
> ... Therefor, any hints feature *will* be used widely
> and in "inappropriate" circumstances. Protestations that
> this wasn't what the feature was meant for will fall on
> deaf ears.
I don't really care about this topic, as I've used Oracle and never
actually made use of its hint system, but I liked knowing it was there.
But what's better here, asking the optimizer to use what is tested with
explain analyze to be a better plan, or to convolute a query so
horribly it's hardly recognizable, in an effort to "trick" the
optimizer?
Someone made a note earlier that any hints made irrelevant by optimizer
improvements would probably need to be removed, citing that as a
maintenence nightmare. But the same point holds for queries that have
been turned into unmaintainable spaghetti or a series of cursors to
circumvent the optimizer. Personally, I'd rather grep my code for a
couple deprecated key-words than re-check every big query between
upgrades to see if any optimizer improvements have been implemented.
Query planning is a very tough job, and SQL is a very high-level
language, making it doubly difficult to get the intended effect of a
query across to the optimizer. C allows inline assembler for exactly
this reason; sometimes the compiler is wrong about something, or
experience and testing shows a better way is available that no compiler
takes into account. As such a high-level language, SQL is inherently
flawed for performace tuning, relying almost entirely on the optimizer
knowing the best path. Here we have no recourse if the planner is just
plain wrong.
I almost wish the SQL standards committee would force syntax for sending
low-level commands to the optimizer for exactly this reason. C has
the "inline" keyword, so why can't SQL have something similar? I
agree, hints are essentially retarded comments to try and persuade the
optimizer to take a different action... what I'd actually like to see
is some way of directly addressing the query-planner's API and
circumvent SQL entirely for really nasty or otherwise convoluted
result-sets, but of course I know that's rather unreasonable.
C'mon, some of us DBAs have math degrees and know set theory... ;)
--
Shaun Thomas
Database Administrator
Leapfrog Online
807 Greenwood Street
Evanston, IL 60201
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com
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: sthomas@leapfrogonline.com
Subject: Re: Hints proposal
In-Reply-To: <200610161200.01913.sthomas@leapfrogonline.com>
* 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