Re: Hints WAS: Index Tuning Features

On Thu, 2006-10-12 at 15:06 +0200, Martijn van Oosterhout wrote:
> On Thu, Oct 12, 2006 at 08:50:04AM -0400, Greg Stark wrote:
> > 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)
>
> We currently construct histograms for data in columns, there's no
> particular reason why we can't do the same for functions. In a similar
> vein, I don't see a reason why you couldn't enable a stats-gathering
> mode where function calls would be instrumented to collect information
> about:
>
> - time of execution
> - distribution of outputs
>
> Which could then be used by the planner. Or more directly:
>
> CREATE HISTOGRAM FOR FUNCTION verify_pk_signature(documenent)
> AS ( true = 99, false = 1 );
>
> (Perhaps DECLARE is the better phrase?).

The CREATE OPERATOR command already has a RESTRICT=res_proc clause which
provides the ability to attach selectivity functions onto an operator.

So this is already possible if you turn radius_authenticate() into an
operator. The function parameters are passed to the selectivity
function, so you can use that to steer the selectivity.

Perhaps this should be allowed on the CREATE FUNCTION command when a
procedure returns boolean.

Greg is right though, there are some times when the default selectivity
won't match what we know to be the case. His example of a function which
might normally be expected to return 99.9% true being used to evaluate a
list of suspected attempts where the return might well be 20% true is a
good one.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com