Re: eWeek Poll: Which database is most critical to

On Wed, 27 Feb 2002 16:24:45 EST, Tom Lane wrote:
> F Harvell <fharvell@fts.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