From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Cc: Denis Lussier <denisl@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: korryd@enterprisedb.com <korryd@enterprisedb.com>
Cc: Jonah H. Harris <jharris@enterprisedb.com>
Cc: Craig A. James <cjames@modgraph-usa.com>
Subject: Re: Simple join optimized badly?
Date: Sun, 8 Oct 2006 16:05:02 -0700
Message-ID: <200610081605.03501.josh@agliodbs.com> (raw)
In-Reply-To: <5f820f750610071850o6ed0b19o162d65d1987b2a8c@mail.gmail.com>
References: <45274A77.7080005@modgraph-usa.com>
<18722.1160236300@sss.pgh.pa.us>
<5f820f750610071850o6ed0b19o162d65d1987b2a8c@mail.gmail.com>
Denis,
> Wouldn't PG supporting simple optmizer hints get around this kinda
> problem? Seems to me that at least one customer posting per week
> would be solved via the use of simple hints.
... and add 100 other problems. Hints are used because the DBA thinks that
they are smarter than the optimizer; 99% of the time, they are wrong.
Just try manually optimizing a complex query, you'll see -- with three
join types, several scan types, aggregates, bitmaps, internal and external
sorts, and the ability to collapse subqueries it's significantly more than
a human can figure out accurately.
Given the availability of hints, the newbie DBA will attempt to use them
instead of fixing any of the underlying issues. Craig's post is a classic
example of that: what he really needs to do is ANALYZE HITLIST_ROWS after
populating it. If he had the option of hints, and was shortsighted (I'm
not assuming that Craig is shortsighted, but just for the sake of
argument) he'd fix this with a hint and move on ... and then add another
hint when he adds a another query which needs HITLIST_ROWS, and another.
And then he'll find out that some change in his data (the sample table
growing, for example) makes his hints obsolete and he has to go back and
re-tune them all.
And then ... it comes time to upgrade PostgreSQL. The hints which worked
well in version 8.0 won't necessarily work well in 8.2. In fact, many of
them may make queries disastrously slow. Ask any Oracle DBA, they'll
tell you that upgrading hint is a major PITA, and why Oracle is getting
away from Hints and has eliminated the rules-based optimizer.
Now, if you were offering us a patch to auto-populate the statistics as a
table is loaded, I'd be all for that. But I, personally, would need a
lot of convincing to believe that hints don't do more harm than good.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
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: josh@agliodbs.com, denisl@enterprisedb.com, tgl@sss.pgh.pa.us, bruce@momjian.us, korryd@enterprisedb.com, jharris@enterprisedb.com, cjames@modgraph-usa.com
Subject: Re: Simple join optimized badly?
In-Reply-To: <200610081605.03501.josh@agliodbs.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