Thread: optimizer hints?

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?

"set seqscan to 'f'" just doesn't do it for me, as I need seqscan in one
case and indexscan in another case inside same query.

Any chance we could have something like
select * from foo(bar), baz(0)

(Where bar is an index on foo, and 0 means don't use any indices.)

I realize the syntax may need to get tweaked a bit, to avoid confusion
with future functions-returning-rels...


Thanks
-alex

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.

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.

            regards, tom lane

Alex Pilosov <alex@pilosoft.com> writes:
> 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.

Precisely why you should leave it to the optimizer to figure
these things out for itself.  Curiously enough, there is a
physical-order-correlation statistic in pg_statistic now ;-)

            regards, tom lane

On Tue, 5 Jun 2001, Tom Lane wrote:

> Alex Pilosov <alex@pilosoft.com> writes:
> > 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.
>
> Precisely why you should leave it to the optimizer to figure
> these things out for itself.  Curiously enough, there is a
> physical-order-correlation statistic in pg_statistic now ;-)
You keep impressing me, Tom. I bow before your superior wisdom :)

-alex

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.