Re: Query works when kludged, but would prefer "best practice" solution

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
Cc: "'Merlin Moncure'" <mmoncure(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Query works when kludged, but would prefer "best practice" solution
Date: 2007-09-18 14:08:39
Message-ID: 3638.1190124519@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca> writes:
> Thanks for the suggestion - this concept is pretty new to me. Can you expand
> a bit on the idea of how to place such a "dummy" constraint on a function,
> and the conditions on which it affects the planner?

Let's say that you know that the function's result column "x" can only
range from 1 to 1000. The planner does not know that, and has no
statistics from which it could guess, so it's going to fall back on
default selectivity estimates for any WHERE clause involving x.
So for instance you could tack on something like

FROM ... (select * from myfunc() where x <= 1000) ...

which will change the actual query result not at all, but will cause the
planner to reduce its estimate of the number of rows out by whatever the
default selectivity estimate for an inequality is (from memory, 0.333,
but try it and see). If that's too much or not enough, you could try
some other clauses that will never really reject any rows, for instance

where x >= 1 and x <= 1000
where x <> -1
where x is not null

Of course this technique depends on knowing something that will always
be true about your data, but most people can think of something...

Now this is not going to affect the evaluation of the function itself at
all. What it will do is affect the shape of a join plan built atop that
function scan, since joins are pretty much all about minimizing the
number of intermediate rows.

> Would this require that
> constraint_exclusion be set on?

No.

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Dave Dutcher 2007-09-18 14:11:27 Re: Query works when kludged, but would prefer "best practice" solution
Previous Message valgog 2007-09-18 10:36:23 Re: Index usage when bitwise operator is used