Re: Index Scans become Seq Scans after VACUUM ANALYSE

Thomas Lockhart wrote:
>
> ...
> > I experienced myself many times, joints have to be rewrited...
> > This is really true for outter joins (LEFT/RIGHT join). And it has to be
> > tested with explain plans.
>
> It is particularly true for "join syntax" as used in outer joins because
> *that bypasses the optimizer entirely*!!! I'd like to see that changed,
> since the choice of syntax should have no effect on performance. And
> although the optimizer can adjust query plans to choose the best one
> (most of the time anyway ;) there is likely to be *only one correct way
> to write a query using join syntax*. *Every* other choice for query will
> be wrong, from a performance standpoint.
>
> That is a bigger "foot gun" than the other things we are talking about,
> imho.

Please keep in mind that before the explicit join syntax "optimization"
was available, my 20-way joins took PostgreSQL forever to complete,
regardless of whether the genetic query optimizer was enabled or not.
Using the explicit join syntax, they return results essentially
instantaneously. Perhaps the optimizer can be used for LEFT/RIGHT joins,
but I would still like the option to use explicit join orders in order
to prohibit the exponential growth in time spend in the optimizer. I'd
prefer to have a gun to shoot myself in the foot with than no gun at
all. Or rather, I'd prefer to have a "foot gun" than a "server gun"

server gun (n.) A tool built solely for shooting a PostgreSQL server
when waiting for the completion of a query using a large number of joins

;-)

Mike Mascari
mascarm(at)mascari(dot)com

>
> - Thomas
>
> foot gun (n.) A tool built solely for shooting oneself or another person
> in the foot.