Re: enforcing a plan (in brief) - Mailing list pgsql-hackers

> pgsql@mohawksoft.com writes:
>> I think that is sort of arrogant. Look at Oracle, you can give the
>> planner
>> hints in the form of comments.
>
> Arrogant or not, that's the general view of the people who work on the
> planner.
>
> The real issue is not so much whether the planner will always get things
> right --- it won't, and no one imagines that it will ever be perfect.
> The real issue is that we have limited manpower, and designing and
> implementing a useful hint facility is a nontrivial project.  (Not to
> mention that maintaining such a thing in the face of frequent,
> fundamental changes to the underlying planner and executor capabilities
> would be an outright nightmare.)
>
> The people who are actually doing the work think their time is more
> usefully spent on improving the planner's intelligence than on devising
> ways to override it.
>

I know I come on strong, and I know I'm probably irritating in many ways,
however, I have been a PostgreSQL user since just after it was named
postgreSQL from Postgres95. I've seen a lot of changes, and almost all of
them have been quite good.

I have over 10 years of using it on various projects. While I have not
been lucky enough to get a gig in which I could contribute more, I do try
to contribute and sometimes it is quite difficult.

The one thing that I think you guys miss is actually using PostgreSQL in
some projects where the company and the deliverables don't give a rat's
ass about whether you use PostgreSQL or Oracle or something else. Over the
years I have beat my head against the walls suggesting features, most of
which eventually have come to PostgreSQL, but every one was a fight.

I have some well founded opinions about PostgreSQL hard earned from real
world situations. Agree or not, I have experience with this database and I
have hit many of its short comings.

One consistent problem is the planner not being able to handle this or
that scenario. At this stage, the *best* way to improve the planner is to
add the ability to place hints in the plan. It *is* good enough for 90% of
the types of queries you would ever want to do. I am dubious that you can
get it demonstrably better in the last 10% or so without making it worse.

Simple hints would go a HUGE way to improving the last 10%. Many of the
"Why doesn't PostgreSQL use my index" questions would go away. Most of the
time Tom spends looking at people's pg_stats info would drop. It would
actually save time.

As a PostgreSQL user, I can tell you with 100% confidence, if I had this
tool, I could do my job easier. I can also tell you that while I have
genuine appreciation for the current quality of the planner, I still would
like to be able to tailor queries specifically to test various approaches
for performance reasons.