Re: how to implement selectivity injection in postgresql

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Wed, Aug 13, 2014 at 9:33 AM, Rajmohan C <csrajmohan(at)gmail(dot)com> wrote:
>> I need to implement Selectivity injection as shown in above query in
>> PostgreSQL by which we can inject selectivity of each predicate or at least
>> selectivity at relation level directly as part of query.

> My plan was to create a boolean operator which always returns true, but
> estimates its own selectivity as 0.001 (or better yet, parameterize that
> selectivity estimate, if that is possible) which can be inserted into the
> place where lower selectivity estimate is needed with an "AND".

That doesn't seem especially helpful/convenient, especially not if you're
trying to affect the estimation of a join clause. The last discussion
I remember on this subject was to invent a special dummy function that
would be understood by the planner and would work sort of like
__builtin_expect() in gcc:

selectivity(condition bool, probability float8) returns bool

Semantically the function would just return its first argument (and
the function itself would disappear at runtime) but the planner would
take the value of the second argument as a selectivity estimate overriding
whatever it might've otherwise deduced about the "condition". So
you'd use it like

SELECT ... WHERE selectivity(id = 42, 0.0001)

and get functionally the same results as for

SELECT ... WHERE id = 42

but with a different selectivity estimate for that WHERE condition.

regards, tom lane