Thread: how to implement selectivity injection in postgresql
<div dir="ltr"><pre class="" style="margin-top:0px;margin-bottom:10px;padding:5px;border:0px;font-size:14px;vertical-align:baseline;font-family:Consolas,Menlo,Monaco,'Lucida Console','LiberationMono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif;overflow:auto;width:auto;max-height:600px;word-wrap:normal;color:rgb(0,0,0);line-height:17.804800033569336px;background:rgb(238,238,238)"> <pre class="" style="margin-top:0px;margin-bottom:10px;padding:5px;border:0px;vertical-align:baseline;font-family:Consolas,Menlo,Monaco,'Lucida Console','LiberationMono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif;overflow:auto;width:auto;max-height:600px;word-wrap:normal;background-image:initial;background-repeat:initial"> <code style="margin:0px;padding:0px;border:0px;vertical-align:baseline;font-family:Consolas,Menlo,Monaco,'Lucida Console','LiberationMono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif;white-space:inherit;background-image:initial;background-repeat:initial"><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">SELECT</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> c1</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">,</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">c2</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">,</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">c3</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">,</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"></span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">FROM</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> T1</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">,</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">T2</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">,</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">T3 </span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">WHERE</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> T1</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">x</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">=</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">T2</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">x</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">AND</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> T2</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">y</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">=</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">T3</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">y</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">AND</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> T1</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">x</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">>=</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"></span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">?</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">selectivity </span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(128,0,0);background:transparent">0.00001</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> </span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">AND</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> T2</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">y</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">></span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"></span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">?</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">selectivity </span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(128,0,0);background:transparent">0.5</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> </span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">AND</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> T3</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">z</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">=</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"></span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">?</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">selectivity </span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(128,0,0);background:transparent">0.2</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> </span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(0,0,139);background:transparent">AND</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"> T3</span><span class=""style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">.</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">w</span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">=</span><spanclass="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent"></span><span class="" style="margin:0px;padding:0px;border:0px;vertical-align:baseline;background:transparent">?</span></code></pre> </pre><p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:14px;vertical-align:baseline;clear:both;color:rgb(0,0,0);font-family:Arial,'Liberation Sans','DejaVuSans',sans-serif;line-height:17.804800033569336px;background-image:initial;background-repeat:initial"> I needto implement Selectivity injection as shown in above query in PostgreSQL by which we can inject selectivity of each predicateor at least selectivity at relation level directly as part of query. Is there any on-going work on this front? Ifthere is no ongoing work on this, How should I start implementing this feature?<p style="margin:0px 0px 1em;padding:0px;border:0px;font-size:14px;vertical-align:baseline;clear:both;color:rgb(0,0,0);font-family:Arial,'Liberation Sans','DejaVuSans',sans-serif;line-height:17.804800033569336px;background-image:initial;background-repeat:initial"><br /></div>
On 13-08-2014 13:33, Rajmohan C wrote: > SELECT c1, c2, c3, FROM T1, T2, T3 > WHERE T1.x = T2.x AND > T2.y=T3.y AND > T1.x >= ? selectivity 0.00001 AND > T2.y > ? selectivity 0.5 AND > T3.z = ? selectivity 0.2 AND > T3.w = ? > > 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. Is there any > on-going work on this front? If there is no ongoing work on this, How > should I start implementing this feature? > Do you want to force a selectivity? Why don't you let the optimizer do it for you? Trust me it can do it better than you. If you want to force those selectivities for an academic exercise, that information belongs to catalog or could be SET before query starts. Start reading backend/optimizer/README. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
On 13-08-2014 15:28, Rajmohan C wrote: > Yeah. I have to do it for my academic research. Is it available in > catalogs? It is to be computed at run time from the predicates in the query > right? > The selectivity information is available at runtime. See backend/optimizer/path/costsize.c. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
On Wed, Aug 13, 2014 at 9:33 AM, Rajmohan C <csrajmohan@gmail.com> wrote:
SELECT c1, c2, c3, FROM T1, T2, T3 WHERE T1.x = T2.x AND T2.y=T3.y AND T1.x >= ? selectivity 0.00001 AND T2.y > ? selectivity 0.5 AND T3.z = ? selectivity 0.2 AND T3.w = ?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. Is there any on-going work on this front? If there is no ongoing work on this, How should I start implementing this feature?
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".
And another one that always returns false, but has a selectivity estimate near 1, for use in OR conditions when the opposite change is needed.
I think that will be much easier to do than to extent the grammar. And probably more acceptable to the core team.
I think this could be done simply in an extension module without even needing to change the core code, but I never got around to investigating exactly how.
Cheers,
Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
> On Wed, Aug 13, 2014 at 9:33 AM, Rajmohan C <csrajmohan@gmail.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