Обсуждение: 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.