Re: Planner hints in Postgresql

On Mon, Mar 17, 2014 at 5:15 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> So, if we're going to support query decorators, we might as well go all
> the way and just offer Oracle-style "use this index". Speaking as
> someone who is often called on to fix performance issues in other
> people's databases, I find major issues with query decorators:

Supplying selectivity estimates in places where the database can't or
wont do them properly itself is a completely different thing from
Oracle style hints. For example, they are much more 'future proof' --
both to schema changes and postgres enhancments -- in the absolute
case the database can peek into your expression in some future version
and generate a better estimate than you can. Aside from that, you're
in no way locked out of future innovations. This could be done in a
relatively clean way: for example, by putting complex quals in an
inlineable function that is decorated with analog of COST/ROWS clause
-- perhaps allowing for a user defined expression to base selectivity
from the input arguments.

OTOH, Hints disable the planner and they are much more complex,
particularly for large queries since you have to consider how
components of the plan relate to one another. Force feeding a plan to
the database is the wrong way to go.

merlin