Re: eWeek Poll: Which database is most critical to

On Wed, 27 Feb 2002 16:24:45 EST, Tom Lane wrote:
> F Harvell <fharvell(at)fts(dot)net> writes:
> > The query plan is not going to be interested at all in
> > the literal value of the parameters and therefore will be the same for
> > any query of the same form.
>
> Unfortunately, this is completely false.
>
> > For example, from above:
>
> > SELECT shirt, color, backorder_qty FROM garments WHERE color like
> > 'BLUE%'
>
> > should become something on the order of:
>
> > SELECT shirt, color, backorder_qty FROM garments WHERE color like
> > '{param0}%'
>
> You managed to pick an example that's perfectly suited to demolish your
> assertion. The query with "color like 'BLUE%'" can be optimized into an
> indexscan (using index quals of the form "color >= 'BLUE' and color <
> 'BLUF'), at least in C locale. The parameterized query cannot be
> optimized at all, because the planner cannot know whether the
> substituted parameter string will provide a left-anchored pattern.
> What if param0 contains '_FOO' at runtime? An indexscan will be
> useless in that case.

Thanks for the feedback. In the example that was used, it was
important to note that the {param0} was the string literal "BLUE" and
not the % "operator". This IMHO ties the query to a left anchored
pattern. I certainly do not think that the "parameter" can be
anything but a literal. Functions and operators would very likely
affect any query plan.

Is it true that the optimizer manipulates the literal? It would
seem that that would require a huge amount of processing (due to
character sets, etc.). It would appear that it would be more viable
to use a simpler optimization that does not manipulate the literal
such as an index quals of the form "color{0,4} == 'BLUE'" than to
generate a range comparison. Of course, this is a very simple query
and I am likely missing a critical concept.

Thanks,
F Harvell