From: Craig A. James <cjames@modgraph-usa.com>
To: Brian Herlihy <btherl@yahoo.com.au>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Simple join optimized badly?
Date: Mon, 09 Oct 2006 20:16:28 -0700
Message-ID: <452B108C.7020703@modgraph-usa.com> (raw)
In-Reply-To: <20061010011042.93217.qmail@web52308.mail.yahoo.com>
References: <20061010011042.93217.qmail@web52308.mail.yahoo.com>
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
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: cjames@modgraph-usa.com, btherl@yahoo.com.au
Subject: Re: Simple join optimized badly?
In-Reply-To: <452B108C.7020703@modgraph-usa.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