On Fri, Nov 14, 2008 at 2:52 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
>> Other systems do it. For example, Oracle tracks column usage and
>> attempts to determine the optimal statistics for that column (based on
>> the queries that used it) on an iterative basis. We don't track
>> column usage at all, so that option wouldn't be quite that easy to
>> implement. Though, there are certain things ANALYZE would be able to
>> determine with a little help, such as knowing to collect more samples
>> for columns it finds extremely skewed data in.
>
> That kind of feedback loops are a bit dangerous. For starters, it would mean
> that your test system would behave differently than your production system,
> just because you run different queries on it. There's also all kinds of
> weird dynamic behaviors that could kick in. For example, a query could run
> fine for the first few hundred times, but then the analyzer notices that a
> certain column is being accessed frequently and decides to increase the
> stats target for it, which changes the plan, for worse. Usually the new plan
> would be better, but the planner isn't perfect.
Oracle already thought of that a long time ago, which is why the plan
has to come out better for it to take effect. As for bad plans, you
obviously haven't used Postgres in production enough to deal with it
continually changing plans for the worse due to index bloat, data
skew, phase of the moon, etc. :)
--
Jonah H. Harris, Senior DBA
myYearbook.com