Обсуждение: parameterized limit statements

I noticed your 8/18 commit to address an issue I raised regarding
parameterized limit statements.  Specifically, prepared statements with
a variable limit would tend to revert to bitmap or seqscan.

I check out cvs tip and am still getting that behavior :(.  So, I had a
look at createplan.c to see what was going on.  Inside makelimit, there
is:

if (count_est != 0)
{double        count_rows;
if (count_est > 0)    count_rows = (double) count_est;else    count_rows = clamp_row_est(lefttree->plan_rows * 0.10);if
(count_rows> plan->plan_rows)    count_rows = plan->plan_rows;if (plan->plan_rows > 0)    plan->total_cost =
plan->startup_cost+        (plan->total_cost - plan->startup_cost)        * count_rows /
plan->plan_rows;plan->plan_rows= count_rows;if (plan->plan_rows < 1)    plan->plan_rows = 1;  
}

Is this correct? plan_rows is assigned (from count_rows) after it is
checked to determine cost.  If this is correct, would you like a test
cast demonstrating the behavior?

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Is this correct?

Sure, what do you think is wrong with it?  plan_rows is initially a copy
of the child node's output-rows estimate, and then it gets modified.
        regards, tom lane

> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > Is this correct?
>
> Sure, what do you think is wrong with it?  plan_rows is initially a
copy
> of the child node's output-rows estimate, and then it gets modified.
OK, just a stab in the dark...not familiar at all with this code (seemed
odd to use value in comparison right before it was assigned).  I am
still getting prepared statements that are flipping to seqscan or bitmap
scan.

The statements are invariably in form of
select a,b,c,d from twhere a >= $1 and     (a >  $1 or  b >= $2) and     (a >  $1 or  b >  $2 or  c >= $3) and     (a >
$1 or  b >  $2 or  c >  $3 or  d >  $4) order by a, b, c, d limit $5;                               ^^ 
If I hardcode $5 to any sub-ridiculous value, I get a proper index plan.
Does your patch assume a limit of 1 or 10% of table rows?

FYI: the planner gets it right about 95% of the time and produces the
best possible plan...an index filtering on a and scanning for b,c,d.

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> The statements are invariably in form of
> select a,b,c,d from t
>     where a >= $1 and 
>         (a >  $1 or  b >= $2) and 
>         (a >  $1 or  b >  $2 or  c >= $3) and 
>         (a >  $1 or  b >  $2 or  c >  $3 or  d >  $4) 
>     order by a, b, c, d limit $5;
>                                 ^^
> If I hardcode $5 to any sub-ridiculous value, I get a proper index plan.
> Does your patch assume a limit of 1 or 10% of table rows?

If it doesn't have a value for the parameter, it'll assume 10% of table
rows, which is what it's done for a long time if the LIMIT isn't
reducible to a constant.

I suspect the real issue here is that whatever you are doing doesn't
give the planner a value to use for the parameter.  IIRC, at the moment
the only way that that happens is if you use the unnamed-statement
variation of the Parse/Bind/Execute protocol.
        regards, tom lane

> >                                 ^^
> > If I hardcode $5 to any sub-ridiculous value, I get a proper index
plan.
> > Does your patch assume a limit of 1 or 10% of table rows?
>
> If it doesn't have a value for the parameter, it'll assume 10% of
table
> rows, which is what it's done for a long time if the LIMIT isn't
> reducible to a constant.
>
> I suspect the real issue here is that whatever you are doing doesn't
> give the planner a value to use for the parameter.  IIRC, at the
moment
> the only way that that happens is if you use the unnamed-statement
> variation of the Parse/Bind/Execute protocol.

hm...I'm using named statements over ExecPrepared.  I can also confirm
the results inside psql with prepare/execute.  I can send you a test
case, but was just wondering if your change to makelimit was supposed to
address this case.

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> hm...I'm using named statements over ExecPrepared.  I can also confirm
> the results inside psql with prepare/execute.  I can send you a test
> case, but was just wondering if your change to makelimit was supposed to
> address this case.

Nope, sorry.
        regards, tom lane

On Mon, 2005-11-07 at 18:43, Tom Lane wrote:
[snip]
> If it doesn't have a value for the parameter, it'll assume 10% of table
> rows, which is what it's done for a long time if the LIMIT isn't
> reducible to a constant.

Is 10% a reasonable guess here ?

Here we use limit in combination with prepared statements to get
something like less than 1% of the table. There are no exceptions to
that in our code... even if the limit amount is a parameter.

Furthermore, the limit amount is always a small number, usually ~ 100,
but never more than 1000. So in my case, we could live with a suboptimal
plan when the percentage would be more than 10%, cause then the table
would be small enough not to matter that much. In turn it has a huge
impact to wrongly guess 10% for a huge table...

I think the best would be to guess 5% but maximum say 5000. That could
work well with both small and huge tables. Maybe those values could be
made configurable... just ideas, not like I could implement this...

[snip]

Cheers,
Csaba.