Re: Hints WAS: Index Tuning Features

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:

> The *right* place, IMHO, for planner information is to decorate the
> tables, columns and relationships so that *every* SQL statement can pick
> that up. If the world changes, you make one change and all your SQL
> benefits. As the analyzers improve, you may be able to just remove those
> declarations entirely but generally I imagine the DB designer will for
> many years know things that cannot be determined by an analyzer.

Not to say this isn't a good idea -- i think it's a great idea. But note that
it doesn't solve some of the use cases of hints. Consider something like:

WHERE NOT radius_authenticate(suspected_hacker)

or

WHERE NOT verify_pk_signature(document_we_have_no_reason_to_doubt)

There's no way you can decorate the radius_authenticate or verify_pk_signature
functions with any hint that would know when you're using it on a value you
expect it to fail or succeed on. In some cases you pass data you expect to
succeed 99.9% of the time and in others data you expect to fail. Only the
author of the query knows what kind of value he's passing and how selective
the resulting expression is.

And while people seem to be worried about OLTP queries this is one area where
I actually think of DSS queries first. OLTP queries run usually relatively
simple and get optimized well. Also OLTP queries only have to be "fast
enough", not optimal. So the planner usually does anm adequate job.

DSS queries are often dozens of lines of plan -- this is where enable_* is
insufficient to test the query and it's where the planner often goes wrong.
And it's where an incremental speed difference can make a big difference with
a report that takes 8 hours or 4 hours. Often these queries are ad-hoc queries
that never will be run again anyways.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com