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
Import Notes
Reply to msg id not found: Pine.BSO.4.10.10106051908030.17529-100000@spider.pilosoft.comReference msg id not found: Pine.BSO.4.10.10106051908030.17529-100000@spider.pilosoft.com | Resolved by subject fallback
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.
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