On Wed, Feb 2, 2011 at 7:03 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Given limited resources as a development community, it's hard to justify
> working on hinting--which has its own complexity to do right--when there are
> so many things that I think are more likely to help *everyone* that could be
> done instead. The unfortunate situation we're in, unlike Oracle, is that
> there isn't a practically infinite amount of money available to fund every
> possible approach here, then see which turn out to work later after our
> customers suffer through the bad ones for a while.
There are actually very few queries where I actually want to force the
planner to use a particular index, which is the sort of thing Oracle
lets you do. If it's a simple query and
random_page_cost/seq_page_cost are reasonably well adjusted, the
planner's choice is very, very likely to be correct. If it's a
complex query, the planner has more likelihood of going wrong, but
forcing it to use an index on one table isn't going to help much if
that table is being used on the inner side of a hash join. You almost
need to be able to force the entire plan into the shape you've chosen,
and that's a lot of work and not terribly robust. The most common
type of "hard to fix" query problem - by far - is a bad selectivity
estimate. Being able to hint that would be worth more than any number
of hints about which indexes to use, in my book.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company