Skip site navigation (1) Skip section navigation (2)

Re: Hints proposal

From: Shaun Thomas <sthomas(at)leapfrogonline(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Hints proposal
Date: 2006-10-16 17:00:01
Message-ID: 200610161200.01913.sthomas@leapfrogonline.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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

In response to

pgsql-performance by date

Next:From: Carlo StonebanksDate: 2006-10-16 17:33:28
Subject: Re: Performance Optimization for Dummies 2 - the SQL
Previous:From: Bruce MomjianDate: 2006-10-16 16:43:43
Subject: Re: Hints proposal

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2006-10-16 17:25:19
Subject: Re: Threaded python on FreeBSD
Previous:From: David FetterDate: 2006-10-16 16:54:24
Subject: Re: BUG #2683: spi_exec_query in plperl returns column names which are not marked as UTF8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group