Re: Delaying the planning of unnamed statements until Bind

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Delaying the planning of unnamed statements until Bind
Date: 2004-05-22 01:49:59
Message-ID: 40AEB1C7.8060509@opencloud.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane wrote:
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>>This leads to my next problem (which was one of the original reasons I
>>went with node replacement): don't we get different performance between
>>a parameterized query and an equivalent unparameterized query in cases
>>such as this? :
>
>
>> SELECT * FROM sometable WHERE field = $1 * 10
>
>
> I don't think the run-time performance would be materially different in
> most cases. What might happen is that if the selectivity functions are
> stupid, they would fail to reduce the comparison value to a constant and
> thus not be able to arrive at a good selectivity estimate, thus leading
> to a bad plan choice.

This appears to be true for all the selectivity functions in
utils/adt/selfuncs.c -- they rely on being given a constant-folded
expression tree.

> However we need not put up with the selectivity
> functions being that stupid. I think it would be reasonable to
> constant-fold expressions involving known parameter values *within the
> context of selfuncs.c only*. This would let us get good estimates
> without giving up the usability of the plan for fresh parameter values.

Ok, so something like this?

1) eval_const_expressions takes a list of parameter values and does
Param to Const replacement if given parameters.

2) The main planner does not pass parameters to eval_const_expressions.

3) When the selectivity functions care about a Const vs non-Const value
and they are dealing with a non-leaf expression node, they call
eval_const_expressions passing the expression tree & the planner-global
parameter values, and then look at the result's Const-ness again.

The alternative is to duplicate much of the logic of
eval_const_expressions into a helper function for anything that cares
about constants.

...

There appear to be a few other places where Const vs. Param affects the
resulting plan (clause_selectivity, LIMIT/OFFSET support, etc). Do the
same thing there?

-O

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-05-22 01:55:12 Re: Delaying the planning of unnamed statements until Bind
Previous Message Tom Lane 2004-05-22 01:25:53 Re: Delaying the planning of unnamed statements until Bind