Re: Index Scans become Seq Scans after VACUUM ANALYSE

mlw <markw(at)mohawksoft(dot)com> writes:
> should we not just allow the developer to place hints in the
> SQL, as:

> select /*+ INDEX(a_id, b_id) */ * from a, b where a.id = b.id;

<<itch>> People have suggested that sort of thing from time to time,
but I have a couple of problems with it:

1. It's unobvious how to tag the source in a way that is helpful
for any but the most trivial queries. Moreover, reasonable sorts
of tags would provide only partial specification of the exact
query plan, which is a recipe for trouble --- an upgraded optimizer
might make different choices, leading to a pessimized plan if some
points are pinned down when others aren't.

2. The tag approach presumes that the query programmer is smarter
than the planner. This might be true under ideal circumstances,
but I have a hard time crediting that the planner looking at today's
stats is dumber than the junior programmer who left two years ago,
and no one's updated his query since then. The planner may not be
very bright, but it doesn't get bored, tired, or sick, nor move on
to the next opportunity. It will pick the best plan it can on the
basis of current statistics and the specific values appearing in
the given query. Every time. A tag-forced query plan doesn't
have that adaptability.

By and large this argument reminds me of the "compiler versus hand-
programmed assembler" argument. Which was pretty much a dead issue
when I was an undergrad, more years ago than I care to admit in a
public forum. Yes, a competent programmer who's willing to work
hard can out-code a compiler over small stretches of code. But no
one tries to write large systems in assembler anymore. Hand-tuned
SQL is up against that same John-Henry-vs-the-steam-hammer logic.
Maybe the current PG optimizer isn't quite in the steam hammer
league yet, but it will get there someday. I'm more interested
in revving up the optimizer than in betting on John Henry.

regards, tom lane