Re: optimizer hints?

On Tue, 5 Jun 2001, Tom Lane wrote:

> Alex Pilosov <alex@pilosoft.com> writes:
> > Are there any plans to implement some way to force optimizer to decide on
> > index scan vs table scan _for a specific table_, and to tell optimizer to
> > use a particular index when operating on a table?
>
> No.  The game plan is to make the optimizer smarter, not to spend time
> on nonstandard cruft like that.  Besides, "optimization hints" have a
> way of becoming pessimization hints over time, as your data shifts and
> the hints don't.
You are absolutely correct, these kludges are just that, kludges.

Yet, sometimes, there's just no way to explain to optimizer certain
patterns you _know_ about your data.

Example: Index scan when data in index is stored in approximately same
order in physical table will be fast. Index scan when index order does not
match table order will be slow. I don't see a way you can explain _that_
to optimizer, nor I really want to. Some things should be left to humans,
as little as possible, but, these knobs really are needed in a
large-database environment...

> I'm about ready to call for alpha testing on the improved pg_statistic
> code, BTW.  If you've got time you might see what the current CVS tip
> thinks about your query.
I'll try that later. Are there any radical changes per chance, such as
more granular index density measurements..?

Thanks.