Gunther Schadow <gunther@aurora.regenstrief.org> writes:
> These and other issues are all nicely tweakable by SQL if you have
> static queries. But if the queries can be in zillions of combinations,
> the problem can't be solved by massaging every single SQL query.
> (And yes, the problem of n-way joins with n > 6, 7, 8, etc. is very
> much a possibility.)
If the queries vary that much, it's tough to believe that you can invent
optimal plans for them more easily than the optimizer can.
But on a more global level: sure the optimizer has shortcomings --- but
I'd rather put my development effort into fixing those shortcomings than
into designing, writing, and maintaining an API that shortcircuits the
optimizer. The costs of having such a feature are not small IMHO. Nor
are the costs of using it on the application side small: you'd have to
write significant code to produce plans that are both nontrivial and
better than the optimizer can do. And then maintain it in the face of
significant version-to-version changes in the API you're using (and in
the underlying facts of what the system can do).
ISTM you have essentially waved your hands and claimed you could write
a nearly-general-purpose application-side planner that will outperform
PG's planner. I'd rather see *you* put that effort into helping fix
PG's planner ;-)
regards, tom lane