Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> In the meantime, the fire is burning. What should the hapless
> owner of the database application do in the meantime? Tell the
> users that it will be better in the next version? As I've said
> before: hints are make it or break it point. Without hints, I
> cannot consider Postgres for the mission critical projects. I am
> managing big databases for living and I flatter myself that after
> more than two decades of doing it, I am not too bad at it.
Well, I've been at it since 1972, and I'm OK with the current
situation because I push hard for *testing* in advance of production
deployment. So I generally discover that leaving a pan of grease on
maximum flame unattended is a bad idea in the test lab, where no
serious damage is done. Then I take steps to ensure that this
doesn't happen in the user world.
We've got about 100 production databases, some at 2TB and growing,
and 100 development, testing, and staging databases. About 3,000
directly connected users and millions of web hits per day generating
tens of millions of queries. Lots of fun replication and automated
interfaces to business partners -- DOT, county sheriffs, local
police agencies, district attorneys, public defenders offices,
Department of Revenue (for tax intercept collections), Department of
Justice, etc. (That was really just the tip of the iceberg.)
Almost all of this was converted inside of a year with minimal fuss
and only a one user complaint that I can recall. Most users
described it as a "non-event", with the only visible difference
being that applications were "snappier" than under the commercial
database product. One type of query was slow in Milwaukee County
(our largest). We tuned seq_page_cost and random_page_cost until
all queries were running with good plans. It did not require any
down time to sort this out and fix it -- same day turnaround. This
is not a matter of hinting; it's a matter of creating a cost model
for the planner which matches reality. (We don't set this or any
other "hint" per query, we tune the model.) When the cost estimates
mirror reality, good plans are chosen.
-Kevin
