Re: Index Scans become Seq Scans after VACUUM ANALYSE

Tom Lane wrote:

> 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.

I am not suggesting that anyone is going to write each and every query with
hints, but a few select queries, yes, people will want to hand tune them.

You are right no one uses assembler to create big systems, but big systems
often have spot optimizations in assembler. Even PostgreSQL has assembler in
it.

No generic solution can be perfect for every specific application. There will
always be times when hand tuning a query will produce better results, and
sometimes that will make the difference between using PostgreSQL or use
something else.

For the two years I have been subscribed to this list, this is a fairly
constant problem, and the answer is always the same, in effect, "we're working
on it." If PostgreSQL had the ability to accept hints, one could say, "We are
always working to improve it, but in your case you may want to give the
optimizer a hint as to what you expect it to do."

It may not be the "best" solution in your mind, but speaking as a long time
user of PostgreSQL, it would be a huge help to me, and I'm sure I am not alone.