Message-ID: <452B108C.7020703@modgraph-usa.com>
Date: Mon, 09 Oct 2006 20:16:28 -0700
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060909)
MIME-Version: 1.0
To: Brian Herlihy <btherl@yahoo.com.au>
CC: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Simple join optimized badly?
References: <20061010011042.93217.qmail@web52308.mail.yahoo.com>
In-Reply-To: <20061010011042.93217.qmail@web52308.mail.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Brian Herlihy wrote:
> PG does support hints actually.. 
> The only thing is, the hints are expressed in an obscure, ad-hoc and
> implementation dependant language.
> 
> For example, the "Don't use index X" hint (the one I used) can be accessed by
> replacing your index with an index on values derived from the actual index...

And then there's 

    select ... from (select ... offset 0)

where the "offset 0" prevents any rewriting between the two levels of query.  This replaces joins and AND clauses where the planner makes the wrong choice of join order or filtering.  I grepped my code and found four of these (all workarounds for the same underlying problem).

Imagine I got run over by a train, and someone was reading my code.  Which would be easier for them to maintain: Code with weird SQL, or code with sensible, well-written SQL and explicit hints?  Luckily for my (hypothetical, I hope) successor, I put massive comments in my code explaining the strange SQL.

The bad applications are ALREADY HERE.  And they're WORSE to maintain than if we had a formal hint language.  The argument that hints lead to poor application is true.  But lack of hints leads to worse applications.

Craig